Number Formatting

[keith37676]

Hi all,

When I write in a number and put it under a custom format of 000 like 2 becomes 002 it becomes 002 in the excel spreadsheet . But if i Put a formula like =(LEFT(A1,FIND(" ",A1)-1) and try to custom format it with 000 like I did before, the excel spreadsheet does nothing. Any suggestions?

 

[Jonmo1]

This is because LEFT Returns a TEXT value, not a NUMBER value. So NUMBER format would be ineffective.

try

=--(LEFT(A1,FIND(" ",A1)-1)

the -- converts "Number Stored as Text" To an actual Number.

 

[Domski]

Hi,

Either:

=VALUE((LEFT(A1,FIND(" ",A1)-1))

or

=(LEFT(A1,FIND(" ",A1)-1)*1

The formula you are using will return a text string so you need to convert it to a value before Excel will apply the format correctly.

Dom