Convert Numbers to Text

[oldblindpew]

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,

 

[YESHWANT]

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

 

[Niek Otten]

One way:

=LEFT(A1&" ",LEN(A1)-1)

You can copy and paste>special>values over the original if you wish

 

[oldblindpew]

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

 

[oldblindpew]

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