Excel 2011- how to store numbers as text?

[rbrian]

The original cell contains numbers with quotes, e.g. '12345' which of course makes it text. I use =REPLACE to remove the quotes, leaving just 12345, which is a number, but still stored as text - even after formatting the cell as a number.

I need to look it up on another sheet which has the same number stored as a number, and =VLOOKUP can't find it! On my work laptop, running Vista and Excel 2003, I can copy the number into NotePad, and copy it back again to find it's magically converted to a number stored as text, and =VLOOKUP works.

I tried the same thing with Lion, Excel 2011, and TextEdit, and it didn't work - the number is still a number. I tried reformatting the cell as text, and the number jumps to the left, so it looks like text, but =VLOOKUP still can't find it.

I need something as simple as NotePad - it seems TextEdit is too clever!

 

[James Craner]

You need to use the Mid function to remove the leading and trailing ' from the text string. The syntax of Mid is MID(text,start_num,num_chars) Start_num is 1, and num_chars will be the length of the text string less 1 (as you don't want the trailing ', which you can get using Len function.

This function then needs to be nested in the Value formula to turn the text string into a value. Hope this helps.

 

[jlc1978]

I need to look it up on another sheet which has the same number stored as a number, and =VLOOKUP can't find it! On my work laptop, running Vista and Excel 2003, I can copy the number into NotePad, and copy it back again to find it's magically converted to a number stored as text, and =VLOOKUP works.

I tried the same thing with Lion, Excel 2011, and TextEdit, and it didn't work - the number is still a number. I tried reformatting the cell as text, and the number jumps to the left, so it looks like text, but =VLOOKUP still can't find it.

I need something as simple as NotePad - it seems TextEdit is too clever!

That is odd - i tried various combinations of the value function and text functions that did not work. Excel must have some weird internal storage method. They'd work once but them not on a recalculation.

 

[rbrian]

Thanks for your reply. I'll have a go tomorrow, or Monday. It'll require a re-write of several sections of the sheet, and I can't be bothered right now!

 

[rbrian]

Oh yes, and one more thing, is there a function that will search an array and find a result not from a specific column? I want to be able to say "Search for C1, in range D1:BG1000, return the cell one to the right of where you found it".

=VLOOKUP doesn't do it, which means I have to use some awkward workarounds - stacking all the column pairs in columns A and B - which get to be over 50,000 rows long.

 

[saberahul]

Oh yes, and one more thing, is there a function that will search an array and find a result not from a specific column? I want to be able to say "Search for C1, in range D1:BG1000, return the cell one to the right of where you found it".

=VLOOKUP doesn't do it, which means I have to use some awkward workarounds - stacking all the column pairs in columns A and B - which get to be over 50,000 rows long.

You said one to the right. Wouldn't that be HLOOKUP instead of VLOOKUP?

 

[rbrian]

You said one to the right. Wouldn't that be HLOOKUP instead of VLOOKUP?

Then I'd have to specify a particular row for the result. It could be any one of a 1000 rows, and any one of 50 columns.

Both =VLOOKUP and =HLOOKUP will only take a result from a specified column or row.

I need =ALOOKUP, but such a thing doesn't exist. There may be an alternative though, I was hoping someone might know.

 

[dooban]

Once quotes are removed, have you tried multiplying the "string number" by one ? It should give you the number.

 

[James Craner]

Oh yes, and one more thing, is there a function that will search an array and find a result not from a specific column? I want to be able to say "Search for C1, in range D1:BG1000, return the cell one to the right of where you found it".

=VLOOKUP doesn't do it, which means I have to use some awkward workarounds - stacking all the column pairs in columns A and B - which get to be over 50,000 rows long.

Well there is not a built in function that can do this, however we can create a User Defined Function using VBA.

The following code creates a UDF called findvalue to paste it in as a VBA Module - Click Tools > Macro > Visual Basic Editor

Then right click on the sheet that you have the data table and choose Insert >Module



On the module Sheet insert the following code

Function FindValue(Search_Value As Variant, Lookup_Range As Range) As String
FindValue = "#N/A" 'If value not found
For Each c In Lookup_Range
If c.Value = Search_Value Then
FindValue = c.Address
Exit For
End If
Next
End Function

Then take a look at my sample sheet for the formula



Note : Table is a defined cell range B3:E14

The findvalue function returns the cell reference as text, the INDIRECT function returns a cell reference and the Offset function is used to return the value in the cell to the right.

Hope this solves the problem for you.

 

[rbrian]

Thanks for your help guys. I've now used =VALUE(MID) to extract the number, then multiplied by 1 - and it still doesn't work! I'll have to continue using notepad to convert the numbers in the lookup sheet to text.

Now for one more question, this one more about ethics than Excel. I started this project, which I estimate will save me around an hour a day, after seeing a colleague on the night shift using the same thing. He refused to share, reasoning (correctly) that if management saw it, they would expect more work out of us.

Now I've moved to the day shift, and decided to build my own spreadsheet. It's been quite successful, thanks to yourselves, Google, and the inspiration of my night shift colleague. Working in the day, naturally I come into contact with managers, who are conspicuous by their absence at night.

They've seen my spreadsheet, and some tools which were heavily modified by the night shift, and are so impressed, they'd like me to make a presentation with a view to rolling it out nationally. It's a feather in my cap, and is likely to lead to promotion. My night shift colleague has asked me not to present anything that he worked on.

Most of the work is my own, but I don't know if I would even have started the project before seeing what was possible. I have taken advice from various sources, including all of you. Do any of you want to veto my presentation, since it contains some of your work? Do you have the right? Should you have the right?

 

[exegete77]

I worked for 8 years in a Fortune 50 company. I reduced one process from 45+ days to less than an hour. I learned much from books, and a couple Excel sites where international MS VIP's participated. Not once did they ever want credit. I had posed a problem to do something that was rather difficult, and time consuming (replace hundreds of items in 30,000 rows). There were several excellent solutions. Finally one person used a method that even the best had not ever tried or used. With modication, it I was able to use it and it accomplished the replacements in less than 2.5 seconds. Again, not once was anyone on the entire board (MrExcel.com) looking for credit let alone a demand not to use.

And I think that VBA code and formulas like this are not copyrighted. And if he did it for the company on company computers, then it does not belong to him or you. It belongs to the company... look at the statement the company has regarding that.

 

[James Craner]

I have no problem with you using any thing that I posted here - I just want to help. I should mention though that the VBA code is not mine, it is something I found on a site like MrExcel, but I can't remember where I got it from as I have had it for a while, but since it was posted to a help forum I am sure he/she would not mind it being used.