Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Convert Numbers to Text
Excel doesn't seem to understand the concept of numeric text strings. If it
looks like a number, Excel seems bent on making it a number. If I import data from some other source, e.g., Part Numbers, or Cost Account Codes, I have trouble with functions like VLOOKUP, because there is not a match between key values, even though they look alike. I have to manually reenter the values in order to convert them back into text, so they will match the values in the lookup table. My question is, when is Excel going to straighten this mess out, and until then, how can I convert an entire column of numbers back into text, short of pressing F2 and Enter five thousand times? Thanks, |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Convert Numbers to Text
Hi oldblindpew,
while importing data itself, why don't you convert the same to text. It is not clear from your post, from where r u importing data into excel ? If it is a *.txt file, while importing the same you can convert the entire column to text in single stroke. click on data - import external data - import data - selected fixed width - next - create your columns by clicking at the desired points - next - select the column you want to convert to text - select column data format at "text" - complete the procedure and you will be at home. if you are importing any other file format, kindly let us know so that we can suggest the remedy for the same. click yes below, if it helps "oldblindpew" wrote: Excel doesn't seem to understand the concept of numeric text strings. If it looks like a number, Excel seems bent on making it a number. If I import data from some other source, e.g., Part Numbers, or Cost Account Codes, I have trouble with functions like VLOOKUP, because there is not a match between key values, even though they look alike. I have to manually reenter the values in order to convert them back into text, so they will match the values in the lookup table. My question is, when is Excel going to straighten this mess out, and until then, how can I convert an entire column of numbers back into text, short of pressing F2 and Enter five thousand times? Thanks, |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Convert Numbers to Text
One way:
=LEFT(A1&" ",LEN(A1)-1) You can copy and pastespecialvalues over the original if you wish -- Kind regards, Niek Otten Microsoft MVP - Excel "oldblindpew" wrote in message ... Excel doesn't seem to understand the concept of numeric text strings. If it looks like a number, Excel seems bent on making it a number. If I import data from some other source, e.g., Part Numbers, or Cost Account Codes, I have trouble with functions like VLOOKUP, because there is not a match between key values, even though they look alike. I have to manually reenter the values in order to convert them back into text, so they will match the values in the lookup table. My question is, when is Excel going to straighten this mess out, and until then, how can I convert an entire column of numbers back into text, short of pressing F2 and Enter five thousand times? Thanks, |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Convert Numbers to Text
Hi Niek, and thanks.
Your method didn't work, although I don't know why. I had a six-character string, and your formula gave me only the first five. However, you got me thinking. Apparently, just about any text function in a formula will just read the source cell as text. These all work: =LEFT(A1,LEN(A1)) =TRIM(A1) (Assuming you don't mind losing any blank characters) =TEXT(A1, "#") (This is a special function for converting numbers into text). But let's say I have a huge column of numbers, formatted General, so Excel sees these as actual numbers. I know they are codes, not numbers in a mathematical sense. If I convert them to text using one of the formulas above, and Copy/Paste Special/Values back into the original column, formatted General, and later someone comes along and edits any of those cells by merely pressing F2, Enter, Excel will convert them right back into numbers again. So I want to do two things. First, I want values converted to text, and second, I want leading apostrophes, to make my data safe forever from the meddling of Excel's General format spec. You'd think this formula would work: ="'"&TRIM(A1) but it doesn't, because somehow it adds the apostrophe as a regular character, rather than a control character. I've discovered that if you make a temporary column, formatted Text, and put just an apostrophe in each cell, and then Copy/Paste Special/Values from your other temporary column that has the formulas (per above) for converting numbers to text, the apostrophe will magically appear at the front of each text value. You can then Copy/Paste Special/Values from that column, back over your original column, and delete your two temporary columns. It may be a journey you shouldn't have to take, and the way may be crooked and devious, but you can get there eventually. --Pew "Niek Otten" wrote: One way: =LEFT(A1&" ",LEN(A1)-1) You can copy and pastespecialvalues over the original if you wish -- Kind regards, Niek Otten Microsoft MVP - Excel "oldblindpew" wrote in message ... Excel doesn't seem to understand the concept of numeric text strings. If it looks like a number, Excel seems bent on making it a number. If I import data from some other source, e.g., Part Numbers, or Cost Account Codes, I have trouble with functions like VLOOKUP, because there is not a match between key values, even though they look alike. I have to manually reenter the values in order to convert them back into text, so they will match the values in the lookup table. My question is, when is Excel going to straighten this mess out, and until then, how can I convert an entire column of numbers back into text, short of pressing F2 and Enter five thousand times? Thanks, |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Convert Numbers to Text
Hi YESHWANT,
Thanks for taking time to reply. My concern in this case is not how to avoid a problem in Excel, but what to do about the problem after it has occurred. And also, to find out if there is any resonance out there with other users, as I feel certain I am not the only one to struggle with this problem for years and years. I did find a method to work around the problem, as seen elsewhere in this thread. Thanks again, --Pew "YESHWANT" wrote: Hi oldblindpew, while importing data itself, why don't you convert the same to text. It is not clear from your post, from where r u importing data into excel ? If it is a *.txt file, while importing the same you can convert the entire column to text in single stroke. click on data - import external data - import data - selected fixed width - next - create your columns by clicking at the desired points - next - select the column you want to convert to text - select column data format at "text" - complete the procedure and you will be at home. if you are importing any other file format, kindly let us know so that we can suggest the remedy for the same. click yes below, if it helps "oldblindpew" wrote: Excel doesn't seem to understand the concept of numeric text strings. If it looks like a number, Excel seems bent on making it a number. If I import data from some other source, e.g., Part Numbers, or Cost Account Codes, I have trouble with functions like VLOOKUP, because there is not a match between key values, even though they look alike. I have to manually reenter the values in order to convert them back into text, so they will match the values in the lookup table. My question is, when is Excel going to straighten this mess out, and until then, how can I convert an entire column of numbers back into text, short of pressing F2 and Enter five thousand times? Thanks, |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert numbers stored as text to numbers | Excel Discussion (Misc queries) | |||
convert numbers stored as numbers to text | Excel Worksheet Functions | |||
Convert numbers stored as text to numbers Excel 2000 | Excel Discussion (Misc queries) | |||
How do I convert numbers stored as text with spaces to numbers | Excel Discussion (Misc queries) | |||
How to convert Excel imported numbers from text to numbers? | Excel Discussion (Misc queries) |