Convert 1.1B to 1,100,000.00

[SteveM]

How do I format cell to convert 1.1B to 1,100,000.00 I have large list with
revenue shown in #.##B and #.##M. Need to convert Billion and Million to
standard numbers.

 

[Sheeloo]

If they are just formatted like that then you need to change the format to
NUMBER.

If the numbers are in Col A then you can test by
=ISNUMBER(A1)
in B1
If the result is TRUE then just change the format
If it is false then use the formul
=IF(RIGHT(A1,1)="B",(LEFT(A1,LEN(A1)-1))*1000000000,IF(RIGHT(A1,1)="M",(LEFT(A1,LEN(A1)-1))*1000000,A1))
in B1 and copy down

 

[MyVeryOwnSelf]

How do I format cell to convert 1.1B to 1,100,000.00 I have large

list with revenue shown in #.##B and #.##M. Need to convert Billion
and Million to standard numbers.

One way is to use
=IF(ISBLANK(A1),"",
IF(RIGHT(A1)="M",1000000*LEFT(A1,LEN(A1)-1),
IF(RIGHT(A1)="B",1000000000*LEFT(A1,LEN(A1)-1),
A1)))
and format it as a number.

 

[Rick Rothstein]

Do your cells contain text values (a number with a letter next to it) or do
they just contain a number with the letter being added by Cell Formatting?

 

[Teethless mama]

Try this:

=IF(ISTEXT(A1),10^(LOOKUP(RIGHT(A1),{"B","K","M"},{9,3,6}))*LEFT(A1,LEN(A1)-1),A1)

 

[Shane Devenshire]

Hi,

Here is an entirely different approach which require no formula or code.

1. Select an empty cell and enter 1000000
2. Copy that cell
3. Press Ctrl+F and click the Format button on the right
4. On the Number tab choose Custom and find or enter your #.##"M" format.
Click OK once.
5. Click the Find All button. The first hit will be highlighted in the
window at the bottom of the Find dialog box.
6. Scroll the list to the bottom, hold down the Shift key and click the last
entry. This should select all the hits.
7. Click Close
8. Choose Edit, Paste Special, Multiply

Repeat these steps for the items formatted as billions.