Find last instance of character in text
Wow! That worked great. I have no idea what the row(indirect... means but
it worked. Thanks a lot!
Allan
"JMB" wrote:
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
|