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

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

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

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

This will give you the position of the last colon in C6

=LOOKUP(2^15,FIND(":",C6,ROW(INDIRECT("1:"&LEN(C6) ))))

therefore to get the 7 digits following:

=MID(C6,LOOKUP(2^15,FIND(":",C6,ROW(INDIRECT("1:"& LEN(C6)))))+1,7)

neither needs to be "array entered"

"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

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

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

Applause to you as well, daddylonglegs! Thanks a bunch -- I'm going to have
to study yours and JMB's formula!

Thanks a bunch.

Allan

"daddylonglegs" wrote:

This will give you the position of the last colon in C6

=LOOKUP(2^15,FIND(":",C6,ROW(INDIRECT("1:"&LEN(C6) ))))

therefore to get the 7 digits following:

=MID(C6,LOOKUP(2^15,FIND(":",C6,ROW(INDIRECT("1:"& LEN(C6)))))+1,7)

neither needs to be "array entered"

"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

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

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

It returns an array of 1 through whatever the length of A1 is, so if Len(A1)
= 7, the Row(Indirect("1:"&Len(A1))) returns an array of 1, 2, 3, 4, 5, 6, 7.

Mid(A1, Row(Indirect("1:"&Len(A1))), 1) = ":"
evaluates each character of A1 and returns True/False (True for characters
that =":"). Excel stores True as 1 and False as 0. So this array of 1's and
0's is multiplied by the Row(Indirect.....) to get an array of 0's and
nonzero numbers (the nonzeros being the character positions of the ":"'s).
Then the Max function is used on this array to return the character position
of the last ":"

Generally, array formulae take longer to calculate, so you should consider
that before using it. After playing around a little out of curiosity, Biff's
suggestion seems to be about 5.5 times faster than the my suggestion and
twice as fast as daddylonglegs.


"KonaAl" wrote:

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

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

It's more "professional". My goal is to become a MVP. I don't think "Biff"
would get much consideration.

Biff

"JMB" wrote in message
...
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

Although I don't think you will need it, I will wish you good luck.


"T. Valko" wrote:

It's more "professional". My goal is to become a MVP. I don't think "Biff"
would get much consideration.

Biff

"JMB" wrote in message
...
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

Thanks!

Biff

"JMB" wrote in message
...
Although I don't think you will need it, I will wish you good luck.


"T. Valko" wrote:

It's more "professional". My goal is to become a MVP. I don't think
"Biff"
would get much consideration.

Biff

"JMB" wrote in message
...
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

Biff

The quality and quantity of your postings, positively "ooze"
professionalism regardless of the "handle" that you use.

The same would apply to others those using just their forename like Max
or others with interesting "handles" such as Daddylonglegs or Teethless
mama ( to use but a few of the participants who regularly provide
quality answers).

For my part, I only use my full name as I am too boring to have another
"handle" - perhaps I should change to
"theartfuldodger"

I wish you well in your quest.

--
Regards

Roger Govier


"T. Valko" wrote in message
...
It's more "professional". My goal is to become a MVP. I don't think
"Biff" would get much consideration.

Biff

"JMB" wrote in message
...
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

Or the Welsh Dragon ... <bg

Good luck, Biff - you would have my vote if I had one.

Pete

Roger Govier wrote:

Biff

The quality and quantity of your postings, positively "ooze"
professionalism regardless of the "handle" that you use.

The same would apply to others those using just their forename like Max
or others with interesting "handles" such as Daddylonglegs or Teethless
mama ( to use but a few of the participants who regularly provide
quality answers).

For my part, I only use my full name as I am too boring to have another
"handle" - perhaps I should change to
"theartfuldodger"

I wish you well in your quest.

--
Regards

Roger Govier


"T. Valko" wrote in message
...
It's more "professional". My goal is to become a MVP. I don't think
"Biff" would get much consideration.

Biff

"JMB" wrote in message
...
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

There's also (code):

From immediate window:

Set temp = Range("B3") ' Your Text 1 say in cell B3
ans = InstrRev(temp,":",,1)
? mid(temp,ans+1,7)
1012600



"JMB" wrote in message
:

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

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

Hi Pete

Or the Welsh Dragon ... <bg
No, don't think I could use that one now. I don't breathe fire any
longer!!
The fire in the belly was extinguished long ago - probably doused by too
much beer<bg

--
Regards

Roger Govier


"Pete_UK" wrote in message
ups.com...
Or the Welsh Dragon ... <bg

Good luck, Biff - you would have my vote if I had one.

Pete

Roger Govier wrote:

Biff

The quality and quantity of your postings, positively "ooze"
professionalism regardless of the "handle" that you use.

The same would apply to others those using just their forename like
Max
or others with interesting "handles" such as Daddylonglegs or
Teethless
mama ( to use but a few of the participants who regularly provide
quality answers).

For my part, I only use my full name as I am too boring to have
another
"handle" - perhaps I should change to
"theartfuldodger"

I wish you well in your quest.

--
Regards

Roger Govier


"T. Valko" wrote in message
...
It's more "professional". My goal is to become a MVP. I don't think
"Biff" would get much consideration.

Biff

"JMB" wrote in message
...
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

Thanks, Roger!

Biff

"Roger Govier" wrote in message
...
Biff

The quality and quantity of your postings, positively "ooze"
professionalism regardless of the "handle" that you use.

The same would apply to others those using just their forename like Max or
others with interesting "handles" such as Daddylonglegs or Teethless mama
( to use but a few of the participants who regularly provide quality
answers).

For my part, I only use my full name as I am too boring to have another
"handle" - perhaps I should change to
"theartfuldodger"

I wish you well in your quest.

--
Regards

Roger Govier


"T. Valko" wrote in message
...
It's more "professional". My goal is to become a MVP. I don't think
"Biff" would get much consideration.

Biff

"JMB" wrote in message
...
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

Thanks, Pete!

Biff

"Pete_UK" wrote in message
ups.com...
Or the Welsh Dragon ... <bg

Good luck, Biff - you would have my vote if I had one.

Pete

Roger Govier wrote:

Biff

The quality and quantity of your postings, positively "ooze"
professionalism regardless of the "handle" that you use.

The same would apply to others those using just their forename like Max
or others with interesting "handles" such as Daddylonglegs or Teethless
mama ( to use but a few of the participants who regularly provide
quality answers).

For my part, I only use my full name as I am too boring to have another
"handle" - perhaps I should change to
"theartfuldodger"

I wish you well in your quest.

--
Regards

Roger Govier


"T. Valko" wrote in message
...
It's more "professional". My goal is to become a MVP. I don't think
"Biff" would get much consideration.

Biff

"JMB" wrote in message
...
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