On Mon, 27 Nov 2006 16:00:02 -0800, KonaAl
wrote:
Hi All,
I need to be able to return an account number (7 digits) from a text string.
The account number is preceded by a colon. I'm very familiar with find,
left, len, right functions, etc. My problem is the there can be several
colons in the string and the position changes. For example:
Text 1
1000000 · Cash & Cash Equivalents:1010000 · Cash Accounts:1012000 ·
IBT:1012600 · IBT Cash {WF}
Text 2
1000000 · Cash & Cash Equivalents:1010000 · Cash Accounts:1013000 · IBT - B
of A
What I need is 1012600 from the first string and 1013000 from the second.
I can't figure out how to obtain the position of the last colon in the string.
TIA,
Allan
In addition to other solutions, you could download and install Longre's free
morefunc.xll add-in from
http://xcell05.free.fr which will give you a host of
useful functions.
You could then use the Regular Expression:
=REGEX.MID(A1,"(?<=:)\d+",-1)
--ron