Just not as quickly as yours does <g

Out of curiosity, if you don't mind, why the name change?

"T. Valko" wrote:

Oh, OK. It does that.

Biff

"JMB" wrote in message
...
But it was only supposed to pick up the ":", the OP said he was already
familiar w/the text functions and just needed to find the last character
position.

Nice suggestion for a non-array solution.

"T. Valko" wrote:

You need to add 1. It picks up the ":".

=MID(A1,MAX((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1) =":")*(ROW(INDIRECT("1:"&LEN(A1)))))+1,7)

Another way: (normally entered)

=MID(A1,FIND("~",SUBSTITUTE(A1,":","~",LEN(A1)-LEN(SUBSTITUTE(A1,":",""))))+1,7)

Biff

"JMB" wrote in message
...
Just change the cell references from A1 to C6.

"KonaAl" wrote:

Thanks for the reply, JMB. Assuming the "1:" s/b ":1", I couldn't get
this
to work. I tried changing the A1 references to C6, for example, and
still
got a #REF! error. Both times I entered as an array.

Even after looking at the help files for ROW and INDIRECT, I can't
figure
this out. Your help is appreciated.

Allan

"JMB" wrote:

One way

=MAX((MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)=":")*( ROW(INDIRECT("1:"&LEN(A1)))))

entered using Cntrl+Shift+Enter or you will get 0 or 1.


"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