How to I format a number to text?

[gkisystems]

I need to format my product item codes (SKU's) as text for my vlookup to work. Some SKU's are strictly whole numbers, some are numbers with decimal points, and some are numbers and letters.

For example, I have a list of several hundred SKU's that look like this:

4116325488</SPAN>

<TBODY>
</TBODY><COLGROUP><COL></COLGROUP>

CL.272</SPAN>

<TBODY>
</TBODY><COLGROUP><COL></COLGROUP>

FV.3715LB</SPAN>

<TBODY>
</TBODY><COLGROUP><COL></COLGROUP>

G4611.21</SPAN>

<TBODY>
</TBODY><COLGROUP><COL></COLGROUP>

L37646-LBV</SPAN>

<TBODY>
</TBODY><COLGROUP><COL></COLGROUP>

CDA911.P134</SPAN>

<TBODY>
</TBODY><COLGROUP><COL></COLGROUP>

4116325488.01</SPAN>

<TBODY>
</TBODY><COLGROUP><COL></COLGROUP>

I tried using =TEXT(cell_number_here,"#.##") and the formula works fine except for whole number SKU's without a decimal point such as 4116325488. For that SKU, the format results in:

4116325488.

with the period at the end of the number. What formula would work so I do not get the period at the end of the whole number SKU's?

 

[rob.barnes01]

Try this:

=IF(NOT(ISNUMBER(FIND(".",A1,1))),TEXT(A1,"#"),TEXT(A1,"#.##"))

Hope this helps.

 

[T. Valko]

I need to format my product item codes (SKU's) as text for my vlookup to work. Some SKU's are strictly whole numbers, some are numbers with decimal points, and some are numbers and letters.

For example, I have a list of several hundred SKU's that look like this:

4116325488</SPAN>

<TBODY>
</TBODY>

CL.272</SPAN>

<TBODY>
</TBODY>

FV.3715LB</SPAN>

<TBODY>
</TBODY>

G4611.21</SPAN>

<TBODY>
</TBODY>

L37646-LBV</SPAN>

<TBODY>
</TBODY>

CDA911.P134</SPAN>

<TBODY>
</TBODY>

4116325488.01</SPAN>

<TBODY>
</TBODY>

I tried using =TEXT(cell_number_here,"#.##") and the formula works fine except for whole number SKU's without a decimal point such as 4116325488. For that SKU, the format results in:

4116325488.

with the period at the end of the number. What formula would work so I do not get the period at the end of the whole number SKU's?

You can convert numeric numbers to TEXT numbers like this:

A1 = 12345

=A1&""

 

[gkisystems]

You can convert numeric numbers to TEXT numbers like this:

A1 = 12345

=A1&""

So simple, yet so perfect! Thank you!

 

[T. Valko]

So simple, yet so perfect! Thank you!

You're welcome!

 

[rob.barnes01]

Wow. Guess I overshot that one a bit ...