On Sun, 5 Feb 2006 18:16:27 -0800, "cj" wrote:

hi Ron

on some days i want to replace cash with office in a1, and a2 still extract
the numbers whether it's 6cash or 6office.


Ah, one of the nice things about regular expressions is that this sort of
modification is easy:

A2: =REGEX.MID(A1,"\d+(?=\s*(cash|office))",,FALSE)
A3: =REGEX.MID(A1,"\d+(?=\s*(stock|inventory))",,FALSE )

Note the portion of the phrase (cash|office)

The pipe | means use either cash or office.
That they are enclosed in parenthesis properly groups them together.

The first function will now extract a number that is followed by zero or more
spaces and then either the word cash or the word office.


--ron