| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find last instance of character in text
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find last instance of character in text
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find last instance of character in text
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find last instance of character in text
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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find last instance of character in text
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 |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find last instance of character in text
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 |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find last instance of character in text
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 |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find last instance of character in text
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 |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find last instance of character in text
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 |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find last instance of character in text
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 |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Find last instance of character in text
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to convert a month to a quarter ...... | New Users to Excel | |||
| Need Excel Function to FIND Text - Help! | Excel Worksheet Functions | |||
| Find text in another workbook and paste if found match - VBA | Excel Discussion (Misc queries) | |||
| Find and replace should work in Excel text boxes | Excel Discussion (Misc queries) | |||
| Find nth instance of a character in a string | Excel Discussion (Misc queries) | |||
Hybrid Mode
