Extract numeric values in range of text cells

[TAPS_MikeDion]

Hi everybody,

Per the title, I need to be able to extract the numeric values in a range of text cells.

For example:
Cells L4 (never changing) - L450 (dynamic range) may have the text "Reimbursement: $30"

There may be text to the left of the word Reimbursement and the dollar amount can be 1 to 3 digits. I need to show the total reimbursement of money within the range of cells in column L.

I realize the simplest solution would be to have the monetary totals in a separate column, but I'm not able to do that with this sheet.

Thank you to anyone who can assist!

-Mike

 

[Joe4]

Does the amount always have a dollar sign in it, and is it always that last part of the string (nothing ever will come after it)?

Are you looking for a formulaic or VBA solution?

 

[TAPS_MikeDion]

Hi Joe,

Yes to both questions, and preferably formulaic.

Thanks!

 

[Joe4]

OK, for row 4, you can use a formula like this:

=MID(L4,FIND("$",L4),LEN(L4))

and copy down for all rows.

That will return the text value "$30".
If you want a numeric value, just add a "+0" to the end of the formula.
That will convert it to a number, and remove the "$". If you want to see the "$" but have it be a number, simply change the formatting of the column to "Currency" (and set decimals to 0).

 

[TAPS_MikeDion]

I think I need to clarify a little more as to what I was requesting, because what you have needs to be put into all of the cells. What I was hoping for was a single formula to put into 1 cell (I442 to be exact) that checks column L (again from row 4 to 450, which 450 is dynamic) that has a reimbursement amount.

For example:
In column L, if say row 25 has "Reimbursement: $50" and row 200 has "UBER Reimbursement: $40", then I was hoping there was a way to just have one formula in cell I442 that scans through column L and finds those totals, which would obviously be 90 in this case.

Thanks again for your help.

 

[Joe4]

OK, that is MUCH different than what I thought you were doing.
Quite frankly, I am not sure how to do that all in one formula. I imagine it may be some sort of array formula.

 

[Eric W]

Maybe:

Book1
	L	M	N	O
1
2
3
4	something			$ 237.00
5	something
6	Reimbursement: $50
7	something
8	Reimbursement: $125
9	something
10	something
11	something
12	something
13	Reimbursement: $62
14
Sheet9

Cell Formulas
Range	Formula
O4	O4	=SUMPRODUCT(--(LEFT(MyData,13)="Reimbursement"),--IFERROR(MID(MyData,FIND("$",MyData),5),0))
Press CTRL+SHIFT+ENTER to enter array formulas.

with the named range MyData defined as:

=Sheet9!L4:INDEX(Sheet9!L:L,MATCH("zzzzz",Sheet9!L:L))

 

[Eric W]

Just noticed that "Reimbursement" might not be on the left:

Book1
	L	M	N	O
1
2
3
4	something			$ 254.00
5	something
6	Reimbursement: $50
7	something
8	Reimbursement: $125
9	something
10	something
11	something
12	something
13	Reimbursement: $62
14	something
15	UBER Reimbursement: $17
16
Sheet9

Cell Formulas
Range	Formula
O4	O4	=SUMPRODUCT(--ISNUMBER(SEARCH("Reimbursement",MyData)),--IFERROR(MID(MyData,FIND("$",MyData),5),0))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[TAPS_MikeDion]

Sorry guys, I was on vacation all last week.

Thanks for your help! I'll try that formula out shortly Eric.

 

[Peter_SSs]

If Eric's data is representative, then perhaps something like this would suffice?

21 06 21.xlsm
	L	M	N	O
1
2
3
4	something			254
5	something
6	Reimbursement: $50
7	something
8	Reimbursement: $125
9	something
10	something
11	something
12	something
13	Reimbursement: $62
14	something
15	UBER Reimbursement: $17
16
Sum

Cell Formulas
Range	Formula
O4	O4	=SUMPRODUCT(--(MID(L4:L20&" $0",FIND("$",L4:L20&" $")+1,3)))