Re: Find last instance of character in text
- From: "T. Valko" <biffinpitt@xxxxxxxxxxx>
- Date: Tue, 28 Nov 2006 02:46:30 -0500
Thanks!
Biff
"JMB" <JMB@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:1B78C11E-2283-4D98-BFD4-F5EBC8D46B91@xxxxxxxxxxxxxxxx
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" <JMB@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:25F4645F-A6B2-4EB3-A928-CB8CAFC6C702@xxxxxxxxxxxxxxxx
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" <JMB@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:D2198C81-9B72-4800-AEBA-1B61B7492D94@xxxxxxxxxxxxxxxx
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" <JMB@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:22F2A1AC-5B02-40E8-A9A8-E0E95AAB71B4@xxxxxxxxxxxxxxxx
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
.
- References:
- Re: Find last instance of character in text
- From: T. Valko
- Re: Find last instance of character in text
- From: JMB
- Re: Find last instance of character in text
- From: T. Valko
- Re: Find last instance of character in text
- From: JMB
- Re: Find last instance of character in text
- From: T. Valko
- Re: Find last instance of character in text
- From: JMB
- Re: Find last instance of character in text
- Prev by Date: Re: Is it possible to emulate a ten-key entry in Excel?
- Next by Date: Re: messagebox in excel 2003
- Previous by thread: Re: Find last instance of character in text
- Next by thread: Re: Find last instance of character in text
- Index(es):
Relevant Pages
|
Loading
