Re: Find last instance of character in text
- From: "Pete_UK" <pashurst@xxxxxxxxxxx>
- Date: 28 Nov 2006 03:51:30 -0800
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" <biffinpitt@xxxxxxxxxxx> wrote in message
news:OqCKhGqEHHA.1748@xxxxxxxxxxxxxxxxxxxxxxx
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: Roger Govier
- Re: Find last instance of character in text
- Prev by Date: Re: Trying to sum multiple occurrences of a word in a table of texts
- Next by Date: Re: Is it possible to emulate a ten-key entry in Excel?
- 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
|
