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

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,212,055
Messages
6,105,643
Members
447,974
Latest member
misspancake

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
Back
Top