$524B and $322M

[Scottmk]

Hi,
Does anyone know an easy way to convert the number format from $524B t
$524,000,000,000? I have too many rows to do it manually. I also som
that are using M for million. Thank

 

[Frank Kabel]

Hi
is this a format or is the 'B' attached to these values as 'Text'. For
the latter one you'll need VBA

 

[Peo Sjoblom]

Edit replace, find what B, replace with 000,000,000, do the same for M
changing the replace with to 000,000

--
For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

 

[Mehrich]

If they are text you can extract the right letter with the
right function, use choose or vlookup to generate a
multiplier and multiply the value of your cell.
best

 

[Frank Kabel]

Hi Peo
but then also replace '$' with nothing (I would assume this is also
entered as text)

For the OP: forget my comment that this would require VBA. Peo's
solution will work just fine

 

[Chip Pearson]

Scott,

Try the following formula:

=IF(RIGHT(A1,1)="B",LEFT(A1,LEN(A1)-1)*1000000000,IF(RIGHT(A1,1)=
"M",LEFT(A1,LEN(A1)-1)*1000000,A1))

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Peo Sjoblom]

Frank,

it doesn't matter, at least not when I tested it, if you enter a value like
$xxx it will automatically be numeric at least on US Excel with US regional
setting and
I tested some different text with trailing B or M and all had the leading $
and they all came
out OK as numeric currency defaulting to this format $#,##0_);[Red]($#,##0)

--
For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

 

[Frank Kabel]

Hi Peo
o.k. again the international setting issue. Didn't work with a German
Excel
But I don't think this will be a probelm for the OP <vbg>

--
Regards
Frank Kabel
Frankfurt, Germany

Frank,

it doesn't matter, at least not when I tested it, if you enter a
value like $xxx it will automatically be numeric at least on US Excel
with US regional setting and
I tested some different text with trailing B or M and all had the
leading $ and they all came
out OK as numeric currency defaulting to this format
$#,##0_);[Red]($#,##0)

Hi Peo
but then also replace '$' with nothing (I would assume this is also
entered as text)

For the OP: forget my comment that this would require VBA. Peo's
solution will work just fine

 

[Scottmk]

Hmm..So far neither the formula or the find replace solution worked.
The formula just spat out the exact same thing and the find replac
solution doesnt work due to the decimals....ie, 323.23M --> See th
problem? Any other suggestions

 

[Frank Kabel]

Hi
try Chip's formula solution

 

[Chip Pearson]

The formula just spat out the exact same thing

My formula works. Are you sure you entered it correctly?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Peo Sjoblom]

Worked with Swedish Excel if I use 320Bkr and replaced the B with 000000000
It seems the OP's values are pure text though maybe even with invisible html
crap

--


For everyone's benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom

Hi Peo
o.k. again the international setting issue. Didn't work with a German
Excel
But I don't think this will be a probelm for the OP <vbg>

--
Regards
Frank Kabel
Frankfurt, Germany

Frank,

it doesn't matter, at least not when I tested it, if you enter a
value like $xxx it will automatically be numeric at least on US Excel
with US regional setting and
I tested some different text with trailing B or M and all had the
leading $ and they all came
out OK as numeric currency defaulting to this format
$#,##0_);[Red]($#,##0)

Hi Peo
but then also replace '$' with nothing (I would assume this is also
entered as text)

For the OP: forget my comment that this would require VBA. Peo's
solution will work just fine


--
Regards
Frank Kabel
Frankfurt, Germany


Peo Sjoblom wrote:
Edit replace, find what B, replace with 000,000,000, do the same
for M changing the replace with to 000,000


Hi,
Does anyone know an easy way to convert the number format from
$524B to $524,000,000,000? I have too many rows to do it
manually. I also some that are using M for million. Thanks