Numbers to Text

[garypea123]

Hi,

I have having a bit of an issue with a formula.

My raw data file I have downloaded appears as text format when there is a 0, 00 prefix or if there is a letter prefix.

However, if there is a standard number then it formats this as a number (natural). My look up file is based only on text format. And so I need a formula to convert the number to text.

Looking on the internet this gives a very basic =TEXT(Cell,"0"). However, when this is appllied to a cell which has a 0, or 00 then it keeps the text format, but removes the 0's

What formula can I use? As you can see below it has converted X90 to text in Y90 (But in Y89 it has removed the 00 which I dont want)

 

[Alex Blakenburg]

See if this works for you.

=IF(ISTEXT(X89),X89,TEXT(X89,"0"))

or

=Trim(X89)

 

[Peter_SSs]

Possibly this?

=X89&""

 

[garypea123]

See if this works for you.

=IF(ISTEXT(X89),X89,TEXT(X89,"0"))

Both work fine, I managed a long winded way which was similar to your IF statement. However, I created another column which was telling me true / false if it was ISTEXT, then an if statement based on a FALSE response. So a bit more long winded, and I should have incorporated in the formula as you have done.

However, the TRIM option is much more simple, and seams to work as expected.

 

[garypea123]

Possibly this?

=X89&""

Thanks, This also works to, and I can understand by adding the "" you are converting this into a text format (or keeping as text)

 

[Alex Blakenburg]

I am sure that if you have a lot of data that Peter's formula will be faster. I have just gotten into the habit of using Trim since it cleans up additional spaces at the same time which is often an issue when you get data from other sources.

 

[garypea123]

I am sure that if you have a lot of data that Peter's formula will be faster. I have just gotten into the habit of using Trim since it cleans up additional spaces at the same time which is often an issue when you get data from other sources.

Thank you for this