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?
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,678
Messages
5,838,736
Members
430,566
Latest member
ChanchalSingh

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