extract letters and sum column

mole999

Well-known Member

Joined

Oct 23, 2004

Messages

10,524

Office Version

1. 2019
2. 2016
3. 2013

Platform

1. Windows

• Oct 18, 2015

• #2

if you change the cell formatting you can display the value without having to add the text USD

Custom Format 0.00, "USD"

 

Upvote 0

Aladin Akyurek

MrExcel MVP

Joined

Feb 14, 2002

Messages

85,210

• Oct 18, 2015

• #3

Row\Col	A​
1​	Base Salary: 8,092.58 USD
2​	Flight Pay: 1,857.14 USD
3​	Per Diem: 137.36 USD
4​	Housing: 3,571.43 USD
5​	Utilities: 91.48 USD
6​	Transport: 412.09 USD
7​	Total: 14162.08

In A7 enter:

="Total: "&SUMPRODUCT(SUBSTITUTE(UPPER(REPLACE(A1:A6,1,FIND(":",A1:A6),"")),"USD","")+0)<strike></strike>

 

Upvote 0

AliGW

Banned

Joined

Mar 9, 2014

Messages

3,628

• Oct 18, 2015

• #4

Create a helper column in C using this copied down:

Code:

=LEFT(B1,FIND(" ",B1)-1)

 

Upvote 0

A

Av8tordude

Well-known Member

Joined

Oct 13, 2007

Messages

1,074

Office Version

1. 2019

Platform

1. Windows

• Oct 18, 2015

• #5

Aladin

Thank you kindly, but the formula does not suit what I was trying to convey. in Column A resides "Base Salary:, Flight Pay:, etc". In column B resides 8092.58 USD, 1857.14 USD.

 

Upvote 0

mole999

Well-known Member

Joined

Oct 23, 2004

Messages

10,524

Office Version

1. 2019
2. 2016
3. 2013

Platform

1. Windows

• Oct 18, 2015

• #6

Av8tordude said:

Aladin

Thank you kindly, but the formula does not suit what I was trying to convey. in Column A resides "Base Salary:, Flight Pay:, etc". In column B resides 8092.58 USD, 1857.14 USD.

Click to expand...

change

=TEXT(8092.58,"#,##0.00 ")& "USD"
to

=TEXT(8092.58,"#,##0.00 ") and custom format

 

Upvote 0

A

Av8tordude

Well-known Member

Joined

Oct 13, 2007

Messages

1,074

Office Version

1. 2019

Platform

1. Windows

• Oct 18, 2015

• #7

Thank you kindly AliGW, but this works only for 1 cell. I'm adding multiple cells (b1:b6)

 

Upvote 0

H

Hashiru

Active Member

Joined

May 29, 2011

Messages

286

• Oct 18, 2015

• #8

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

 

Upvote 0

A

Av8tordude

Well-known Member

Joined

Oct 13, 2007

Messages

1,074

Office Version

1. 2019

Platform

1. Windows

• Oct 18, 2015

• #9

mole999 said:

change

=TEXT(8092.58,"#,##0.00 ")& "USD"
to

=TEXT(8092.58,"#,##0.00 ") and custom format

Click to expand...

I can't do this because the "USD" changes

 

Upvote 0

Aladin Akyurek

MrExcel MVP

Joined

Feb 14, 2002

Messages

85,210

• Oct 18, 2015

• #10

Av8tordude said:

Aladin

Thank you kindly, but the formula does not suit what I was trying to convey. in Column A resides "Base Salary:, Flight Pay:, etc". In column B resides 8092.58 USD, 1857.14 USD.

Click to expand...

Modify it as:

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

 

Upvote 0