How to extract numerical value after a certain string within a sentence in Excel cell?

0

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!

alchuang

Posted 2013-02-28T20:08:51.560

Reputation: 103

What is the maximum number that you would encounter in the text string in the cell (if there is one)? – chuff – 2013-02-28T20:20:27.290

0-8.5 with increments of 0.5, (0, 0.5, 1.0, ... 7.5, 8.0, 8.5) – None – 2013-02-28T20:23:18.943

Answers

2

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),"")

barry houdini

Posted 2013-02-28T20:08:51.560

Reputation: 10 434

A very helpful answer. I modified it to use SEARCH instead of FIND (case sensitive) because the text being looked for had variations in being all lower case, initial cap then lower or all caps. – None – 2014-12-02T00:24:45.023

Thank you so much! It worked great, however, is there a way that the function just displays null (blank) when ABS is not found? – None – 2013-02-28T20:31:31.710

If you have Excel 2007 or later you can wrap an IFERROR function around it - I edited my reply – barry houdini – 2013-02-28T21:10:20.680

Asked: 2013-02-28T20:08:51.560

Viewed: 2 317 times

Active: 2014-12-02T00:24:45.023