Extract Value from String

[copyboy007]

I pasted a table in to Excel.

One column has numbers ending in the same string, " usd".

For example:

5.45 USD
10.98 USD
5.77 USD

How can the numerical portion be extracted as values into another column?

 

[markmzz]

Maybe this:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;;">Data</td><td style="text-align: center;;">Result</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">5.45 USD</td><td style="text-align: right;;">5.45</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">10.98 USD</td><td style="text-align: right;;">10.98</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">5.77 USD</td><td style="text-align: right;;">5.77</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">*********</td><td style=";">*********</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet3</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B2</th><td style="text-align:left">=SUBSTITUTE(<font color="Blue">A2,"USD",""</font>)+0</td></tr></tbody></table></td></tr></table><br />
Markmzz

 

[copyboy007]

I tried it, but get a "#VALUE!" result.

 

[markmzz]

I tried it, but get a "#VALUE!" result.

Try this:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;;">Data</td><td style="text-align: center;;">Result</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">5.45 usd</td><td style="text-align: right;;">5.45</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style=";">10.98 usd</td><td style="text-align: right;;">10.98</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style=";">5.77 usd</td><td style="text-align: right;;">5.77</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">*********</td><td style=";">*********</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet3</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B2</th><td style="text-align:left">=SUBSTITUTE(<font color="Blue">UPPER(<font color="Red">A2</font>),"USD",""</font>)+0</td></tr></tbody></table></td></tr></table><br />
Markmzz

 

[Trebor76]

Another way:

=VALUE(LEFT(A2,SEARCH(" ",A2)-1))

Robert

 

[copyboy007]

To: Markmzz

BINGO!!! Thanks so much. It's all in the details.

I'll try your solution as well, Trebor76!

Thanks to you both.

 

[markmzz]

To: Markmzz

BINGO!!! Thanks so much. It's all in the details.

I'll try your solution as well, Trebor76!

Thanks to you both.

Copyboy007,

Yes, it's in the details.

Thank you for the feedback and until the next formula.

Markmzz

 

[T. Valko]

I pasted a table in to Excel.

One column has numbers ending in the same string, " usd".

For example:

5.45 USD
10.98 USD
5.77 USD

How can the numerical portion be extracted as values into another column?

Here's another one.

Book1
	A	B
2	5.45 USD	5.45
3	10.98 USD	10.98
4	5.77 USD	5.77
Sheet1

This formula entered in B2 and copied down:

=--LEFT(A2,LEN(A2)-4)