Number Formatting

keith37676

New Member
Joined
Apr 11, 2007
Messages
11
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?
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
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

Well-known Member
Joined
Jan 18, 2005
Messages
7,292
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
 

Watch MrExcel Video

Forum statistics

Threads
1,128,057
Messages
5,628,354
Members
416,313
Latest member
Dan99321

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top