• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Extracting number including decimal

pinecrest

New Member
Hello, I'm trying to extract the number after the text AC including the decimals, but not the numbers afterwards. The string is below. I would like to get the number 40.16 extracted into a separate column. Thank you.
EAST OF NUCKOLS RD AC 40.16 76 B2 14
 
hi @pinecrest ,

Suppose your Text start A2 to A100 then type 40.16 in B2

Select B2:B100 and Press Ctrl + E

or

Data Tab > Flash Fill Option

or formula solution

Cell B2:
=MID(A2,FIND("AC ",A2)+3,FIND(" ",A2,FIND(" ",A2,FIND("AC ",A2)+1)+1)-(FIND(" ",A2,FIND("AC ",A2)+1)+2)+1)
 

Attachments

  • flash fill .xlsx
    11 KB · Views: 6
The solution provided by Bosco is somewhat different using European settings
=LOOKUP(9E+307;0+MID(A2;FIND("AC";A2)+3;{1\2\3\4\5}))
And the decimal point in the data has to be replaced with a comma one way or another...
 
Back
Top