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.

To convert 1.1B to 1,100,000.00 in Microsoft Excel, you can use the following steps:

1. Select the cell(s) that contain the revenue data you want to convert.
2. Right-click on the selected cell(s) and choose "Format Cells" from the context menu.
3. In the "Format Cells" dialog box, select "Custom" from the list of categories.
4. In the "Type" field, enter the following format code: #,##0.00,,"M";[Red]-#,##0.00,,"M";#,##0.00,,"B";[Red]-#,##0.00,,"B"
5. Click "OK" to apply the custom format to the selected cell(s).

This custom format code will convert any revenue data that is displayed in the format of #.##B or #.##M to a standard number format with commas and two decimal places. For example, 1.1B will be converted to 1,100,000.00 and 1.23M will be converted to 1,230,000.00.

Note that the custom format code also includes color formatting for negative numbers. If you don't need this feature, you can remove the [Red] sections from the code.

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 formula
=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

"SteveM" wrote:

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.

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.

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?

--
Rick (MVP - Excel)


"SteveM" wrote in message
...
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.

Try this:

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



"SteveM" wrote:

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.

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.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"SteveM" wrote:

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.