extract letters and sum column

[Av8tordude]

Each cell (B1 - B6) is formatted as...

=TEXT(8092.58,"#,##0.00 ")& "USD" (i.e. 8092.58 USD)

I would like to remove the text (USD) "sum column B. Is there a formula that can remove the text and sum the column?

Thank you kindly for helping.


Base Salary: 8,092.58 USD
Flight Pay: 1,857.14 USD
Per Diem: 137.36 USD
Housing: 3,571.43 USD
Utilities: 91.48 USD
Transport: 412.09 USD
Total: $0.00

 

[Av8tordude]

You will need to copy and paste the values in cells B1 - B6 to C1 - C6 using the option paste as values and then select C1 -C6 and do the following:
1. Go to the data tab
2. on the Data tools click text to column
3. Click next (accept the default delimiter)
4. Check the option space under delimiters
5. Click Finish
6. You can now work as required

Thank you kindly, but I was trying to avoid using another column.

 

[Hashiru]

This solution worked in only one cell because you are hard coding you formula. Instead of putting the values directly into the formula use cell reference for the value parameter in the function TEXT. By working this way you can input the formula in one cell and drag down the column.

Hope this helps.

 

[Av8tordude]

Modify it as:

="Total: "&SUMPRODUCT(SUBSTITUTE(UPPER(B1:B6),"USD","")+0)

PERFECT!!! Thank you Aladin!

Cheers

 

[Aladin Akyurek]

PERFECT!!! Thank you Aladin!

Cheers

You are welcome.