Extract number that comes before a certain word in an alphanumeric cell

[Pearla]

Hi all,

I'm hoping someone can help me out. I'm looking for a function that extracts a set of number that comes before the word "trips" in an alphanumeric cell. The cells contain clutter of words and numbers that sometimes the number (that comes before the word "trips") is at the beginning of a cell or sometime it's at the end of a cell.
I did a bit of research and found a support page from MVP in ms office website but it doesn't give me the result that I need. http://office.microsoft.com/en-us/ex...001154901.aspx

Example:

E2 = 200 Trips - Structural
E3 = Wheel - 300 Trips

Result display:

F2 = 200
F3 = 300

Thank you in advance.
Cheers,
Pearl

[Dennis7849]

You should attach a sample of the workbook. The filter can be put inplace and sent back to you.

[Pearla]

Attached a file.

Cheers,
Pearl

[abousetta]

Using your example, if all the numbers are three digits, you can simply use this formula

Please Login or Register  to view this content.

[Pearla]

Using your example, if all the numbers are three digits, you can simply use this formula

Please Login or Register  to view this content.

Thank you for the reply.

Any hint on if the number is not always 3 digits?

Cheers.

[Fotis1991]

Hi

Pls, give a try to this.

=LOOKUP(10^5;MID(E2;FIND("Trips";E2)-{1;2;3;4;5};{1;2;3;4;5})+0)

Note: Change the semi-colons to gomma, if you have to do this.

[Pearla]

Thank you muchly. It works.