| Home |
| Search |
| Today's Posts |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find last instance of character in text
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 |
| 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) | |||
Threaded Mode
