Extract number before specific text

[jondesta]

How would I pull ".041 ACRES" and "2.0 ACRES" out of the same column from both strings shown below? As you can see ACRES is in there twice but in some strings the first ACRES does not exist. I have been trying to find how to pull the number before ACRES and ACRES but cannot figure it out. The second string shows what some of the other cells have, if all were like that it would be easy. They all follow the same format of <# ACRES> in all strings so trying to pull the word ACRES and the number that precedes it.

Any help would be appreciated, been doing it the hard way by changing first instance of ACRES to a different word then key on ACRES and remove all text after, then grab everything after last "," or ")". You can see the issues with that and it is very time consuming.

BLK JG K-7 WOODGLEN ACRES S13 (A125) .041 ACRES (SADDLE PATH EAST OF LT 809)

ASG A376 MCGAHN, TREY A,TRACT 3-4, 2.0 ACRES

Jon

 

[Amit Desai]

Hi,

Did you try flash fill? that may help.

Regards,
Amit Desai.

 

[jasonb75]

I don't think that there is such thing as an easy way to do what you need, I came up with this but it's a bit clumsy.

Book1
	A	B
2	BLK JG K-7 WOODGLEN ACRES S13 (A125) .041 ACRES (SADDLE PATH EAST OF LT 809)	.041 ACRES
3	ASG A376 MCGAHN, TREY A,TRACT 3-4, 2.0 ACRES	2.0 ACRES
Sheet7

Cell Formulas
Range	Formula
B2:B3	B2	=TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",LEN(A2))),AGGREGATE(15,6,SEARCH(ROW($1:$9)-1&REPT(" ",LEN(A2))&"ACRES",SUBSTITUTE(A2," ",REPT(" ",LEN(A2)))),1)-LEN(A2),3*LEN(A2)))

 

[Rick Rothstein]

Assuming you text is never longer than 300 characters, here is another formula that you can try...

=TRIM(RIGHT(SUBSTITUTE(TRIM(LEFT(RIGHT(SUBSTITUTE(A2,"ACRES",REPT(" ",300)),600),300))," ",REPT(" ",300)),300))&" ACRES"

 

[jasonb75]

Good one, Rick! That's a lot cleaner than mine.

I just hope that the information in post 1 is not misleading

but in some strings the first ACRES does not exist.

 

[jondesta]

Wow, thank you both! Looking at these I would not have come up with the solution. Both work and do exactly what I need and more importantly saves sooo much time and possibility for errors. I have read these forums many times always finding what I needed, but this I couldn't find and was my first post. This site never disappoints, thank you both again.

Jon