Convert Numbers to Strings - Need Formula

Hi All,

I need to convert imported alpha-numeric product codes to strings.

Where the code contains a letter or other symbol, that is trivial, since it will already be a string.

However, where the code is numeric, I am having trouble.

The codes come in as an export into excel from another system. Unfortunately, there is no way to specify that the export comes in as a text string (setting cells to TEXT format would be an example of this, but it doesn't work in this scenario).

I am then referencing the import, and need a formula that takes the codes, and converts to strings, so for example, I have tried:

=Text(A1,"General")

This works on most items, but where the code is a long numeric (e.g. 432200697940) the formula returns "4.32201E+11", whereas I need "432200697940".

I then tried:

=Text(A1,"0")

This works on the above example, but it converts 500.12 into "500" which is no good.

If I use:

=Text(A1,"0.00")

then it converts 800 into "800.00" so that's no good either.


Can anyone tell me a format code that will work?

Thanks,

Alan.

Hi All,

After typing the above (possibly DUE to typing the above), I had an idea, and it appears it works:

=IF(ISNUMBER(B2),IF(B2=INT(B2),TEXT(B2,"0"),INT(B2)&MID(B2,LEN(B2)-LEN(INT(B2)),999)),B2)

It lacks elegance, but I think it covers all cases.

Any comments (or improvements)?

Thanks,

Alan.

Hi SixthSense,

Yep. Actually, mine contains an error, but I think this fixes it:

=IF(ISNUMBER($B2),IF($B2=INT($B2),TEXT($B2,"0"),INT($B2)&MID($B2,LEN(INT($B2))+1,999)),$B2)

Not sure if this is exactly logically equivalent to yours though?

Alan.

Originally Posted by nflsales

=A1&""
TRY THIS, I am not sure just trail

{Buries head in hands and weeps}

I'll check too, but it looks good to me so far....

Alan.

Hi All,

Just confirming that I have not found any example where nflsales solution fails, so I am marking this thread as solved.

Thanks,

Alan.