Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I want to extract the numerical value immediately following the string "ABS" (Case sensitive) within a bulk of text in a cell from an Excel table.

Not all the cell may contain the the string I'm looking for, so a filter is required.

The contents of the cell look something like this:

Lorem ipsum dolor sit amet, consectetur adipiscing elit. Morbi ante mi, laoreet nec tristique et, sodales at dui. Vestibulum ullamcorper augue at nibh auctor laoreet ornare nisi posuere. Aliquam convallis nisl auctor mauris pharetra faucibus. Nullam nulla dolor, varius viverra facilisis nec, viverra in tortor. Integer sollicitudin, elit in tempor interdum, sapien justo luctus justo, id varius nisi mauris at ipsum. Vivamus tincidunt neque sed felis accumsan tempus.

ABS: 1.5, ADS: 2.5, SR: 11

Pellentesque habitant morbi tristique senectus et netus et malesuada fames ac turpis egestas. Integer vestibulum felis et lorem pretium molestie. Nulla imperdiet, magna vel accumsan ornare, augue quam ornare justo, vitae volutpat turpis orci quis nibh. Nullam vitae diam et ligula commodo pretium. Etiam et luctus dui.

The trouble is since the contents are entered manually, they may vary in structure, with slight variations such as:

ABS : 1.5 (Space after ABS)

or

ABS is 1.5 ('is' instead of colon)

or

ABS = 1.5 ('=' instead of colon)

or

ABS 1.5 (numerical value immediately after text without colon)

....etc

The numerical value is between 0-8.5 with increments of 0.5, (i.e. 0, 0.5, 1.0, ... 7.5, 8.0, 8.5).

I have tried a combination of left() and find() functions but don't know how to just extract the numbers after finding the string "ABS". If someone can help or point me in a direction, I would greatly appreciate it. Thanks!

share|improve this question

migrated from stackoverflow.com Mar 1 '13 at 20:15

This question came from our site for professional and enthusiast programmers.

    
What is the maximum number that you would encounter in the text string in the cell (if there is one)? –  chuff Feb 28 '13 at 20:20
    
0-8.5 with increments of 0.5, (0, 0.5, 1.0, ... 7.5, 8.0, 8.5) –  alchuang Feb 28 '13 at 20:23

1 Answer 1

up vote 2 down vote accepted

This formula finds ABS....then finds the first numeric value after that and then takes the number that starts there (up to 5 characters)

=LOOKUP(10^10,MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&1234567890,FIND("ABS",A1))),{1,2,3,4,5})+0)

Assumes data in A1

If the number might be longer than 5 characters then extend the {1,2,3,4,5} part

If you want to avoid error if ABS isn't found then try wrapping in IFERROR function

=IFERROR(LOOKUP(10^10,MID(A1,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A1&1234567890,FIND("ABS",A1))),{1,2,3,4,5})+0),"")

share|improve this answer
    
Thank you so much! It worked great, however, is there a way that the function just displays null (blank) when ABS is not found? –  alchuang Feb 28 '13 at 20:31
    
If you have Excel 2007 or later you can wrap an IFERROR function around it - I edited my reply –  barry houdini Feb 28 '13 at 21:10

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.