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

databasemicrosoft excelmicrosoft-office

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!

Best Answer

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

Related Question