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

 

[TAPS_MikeDion]

Hi Eric,

This is beyond my knowledge of Excel, so please bear with me.

I've tried what you posted, but it is not working for me. Am I correct in assuming that "zzzzz" in the defined named range should be "Reimbursement"? However, It didn't work either way. Also, the name of the sheet is Directory, which I replaced Sheet9 with.

For the named range MyData, I used:
=Directory!L4:INDEX(Directory!L:L,MATCH("Reimbursement",Directory!L:L))

In cell I442, I used your formula:
=SUMPRODUCT(--ISNUMBER(SEARCH("Reimbursement",MyData)),--IFERROR(MID(MyData,FIND("$",MyData),5),0))

The cell I442 result is:
#VALUE!

According to the Excel help, the #VALUE! error is very general, and it can be hard to find the exact cause of it, which to me means I'll probably not figure it out on my own. LOL

 

[Radoslaw Poprawski]

there would be way easier faster version if only you would update to office 365.
Then here I already posted few formulas for extracting numbers from text

 

[TAPS_MikeDion]

Hi Peter,

I could be wrong, but your formula appears to be something I need to enter per row, which I am unable to do.

 

[TAPS_MikeDion]

Hi Radoslaw,

Unfortunately, it's my boss' computer and he does not want to upgrade to a monthly/annual contract.

 

[TAPS_MikeDion]

Hi Peter,
After looking more closely at your formula, I see that I was wrong. I tried your formula and it's coming VERY close to what I need. I changed the L4:L20 in your formula to L4:L428, but the only thing is that there are other dollar amounts in column L that are NOT Reimbursement amounts, which I need to avoid.

Two questions:
1. Can your formula be set to evaluate from L4 to L? (i.e. dynamic)
2. Is there a way to isolate the "Reimbursement" totals only? The exact text used in column L for those totals is "Reimbursement: $" and then the dollar amount, which does not go above $999

Thank you!

 

[Eric W]

Maybe this hybrid of Peter's and my formula will work for you:

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

Cell Formulas
Range	Formula
O4	O4	=SUMPRODUCT(--ISNUMBER(SEARCH("Reimbursement",MyData)),--(MID(MyData&" $0",FIND("$",MyData&" $0")+1,3)))

The named Range MyData should be defined as:

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

and it should be "zzzzz", not Reimbursement. The way that works is MATCH is looking for "zzzzz" in column L, and if it can't find it, it returns the last row with data in it. That's how we're dynamically defining the range. "zzzzz" should be a value that's "bigger than" anything else in the column. After you define the range, go back into the Name Manager and make sure that the range still refers to the right column. I've found that it sometimes mysteriously changes the range, and it takes 2 or 3 times to make sure it sticks.

 

[TAPS_MikeDion]

Thanks Eric, I'll try that.

Yes, I noticed it took me several tries before the range actually saved, referring to the correct column!

 

[TAPS_MikeDion]

Hi Eric,

I don't know if you can hear it on your end, but over here...the angels are singing!!!

Thank you BOTH for your help, it works great!

 

[Eric W]

Glad we could help! ???

 

[Peter_SSs]

Two questions:
1. Can your formula be set to evaluate from L4 to L? (i.e. dynamic)
2. Is there a way to isolate the "Reimbursement" totals only? The exact text used in column L for those totals is "Reimbursement: $" and then the dollar amount, which does not go above $999

In relation to 1, Eric has given you a way and I don't know your particular circumstances but in general, instead of trying to tie down that range exactly, I would just choose a range that is big enough to accommodate any expected data. For example if I expected at most a few hundred rows, I would set the range to be, say, 1000.

In relation to 2, another option could possibly be as shown below.

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

Cell Formulas
Range	Formula
O4	O4	=SUMPRODUCT(IFERROR(--MID(L4:L1000,FIND("Reimbursement:",L4:L1000)+16,3),0))