$524B and $322M

S

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
 
F

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
 
P

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
 
M

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
 
F

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
 
C

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
 
P

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
 
F

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


Peo said:
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)


Frank Kabel said:
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
Click to expand...
Click to expand...
 
S

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
 
C

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
 
P

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

Frank Kabel said:
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


Peo said:
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)


Frank Kabel said:
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
Click to expand...
Click to expand...
Click to expand...
 
Top