Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
FIND 2nd character in a string
how do i use the FIND function to get the 2nd character?
for eg: A1 has 005/0101/78445945/2002 the entire column has strings like the above.... i want only those characters after the 2nd "/" and before the 3rd "/" what formula to use? in the above result, the answer should be - 78445945 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
FIND 2nd character in a string
You can select the column..Data Text to Columns and separate the pieces to
columns....If you are looking for a formula below is one way.. =TRIM(LEFT(SUBSTITUTE(MID(A1,FIND("/",A1,FIND("/",A1)+1)+1,LEN(A1)),"/",REPT(CHAR(32),LEN(A1))),LEN(A1))) If this post helps click Yes --------------- Jacob Skaria "Fuzzy" wrote: how do i use the FIND function to get the 2nd character? for eg: A1 has 005/0101/78445945/2002 the entire column has strings like the above.... i want only those characters after the 2nd "/" and before the 3rd "/" what formula to use? in the above result, the answer should be - 78445945 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
FIND 2nd character in a string
On Mon, 31 Aug 2009 02:31:01 -0700, Fuzzy
wrote: how do i use the FIND function to get the 2nd character? for eg: A1 has 005/0101/78445945/2002 the entire column has strings like the above.... i want only those characters after the 2nd "/" and before the 3rd "/" what formula to use? in the above result, the answer should be - 78445945 =TRIM(RIGHT(SUBSTITUTE(LEFT(A1,FIND(CHAR(1), SUBSTITUTE(A1,"/",CHAR(1),3))-1),"/",REPT(" ",99)),99)) or, you could use the Data/Text-to-columns wizard with "/" as the delimiter, and use the third column. --ron |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
FIND 2nd character in a string
Hi,
Suppose the string you are looking for 78445945 is always 8 characters, then =MID(A1,FIND("/",A1,FIND("/",A1)+1)+1,8) -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Fuzzy" wrote: how do i use the FIND function to get the 2nd character? for eg: A1 has 005/0101/78445945/2002 the entire column has strings like the above.... i want only those characters after the 2nd "/" and before the 3rd "/" what formula to use? in the above result, the answer should be - 78445945 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
FIND 2nd character in a string
Hi Fuzzy,
The Substitute function allows you to specify an instance # so that you can identify where the 2nd instance of "/" is: SUBSTITUTE(text,old_text,new_text,instance_num) Substitute("\",a1,"unique character of your choice",2) Then you can use Find: Find("unique character of your choice",(substitute("\",a1,"unique character of your choice",2),1) Hope this helps. Peggy "Fuzzy" wrote: how do i use the FIND function to get the 2nd character? for eg: A1 has 005/0101/78445945/2002 the entire column has strings like the above.... i want only those characters after the 2nd "/" and before the 3rd "/" what formula to use? in the above result, the answer should be - 78445945 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
FIND 2nd character in a string
Peggy - Thanks a ton!!!! Was simple and worked!!
Btw, the syntax for substitute was slightly wrong... :) Correct - Substitute(a1,'/',"unique character of your choice",2) "pshepard" wrote: Hi Fuzzy, The Substitute function allows you to specify an instance # so that you can identify where the 2nd instance of "/" is: SUBSTITUTE(text,old_text,new_text,instance_num) Substitute("\",a1,"unique character of your choice",2) Then you can use Find: Find("unique character of your choice",(substitute("\",a1,"unique character of your choice",2),1) Hope this helps. Peggy "Fuzzy" wrote: how do i use the FIND function to get the 2nd character? for eg: A1 has 005/0101/78445945/2002 the entire column has strings like the above.... i want only those characters after the 2nd "/" and before the 3rd "/" what formula to use? in the above result, the answer should be - 78445945 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
FIND 2nd character in a string
Perfect!!!
It worked!! Thank you.... Was wondering how to get the second "/" in that mid formula.... Coz i had used the mid as well as the find formula... just dint know how to get the 2nd "/" God Bless! "Shane Devenshire" wrote: Hi, Suppose the string you are looking for 78445945 is always 8 characters, then =MID(A1,FIND("/",A1,FIND("/",A1)+1)+1,8) -- If this helps, please click the Yes button. Cheers, Shane Devenshire "Fuzzy" wrote: how do i use the FIND function to get the 2nd character? for eg: A1 has 005/0101/78445945/2002 the entire column has strings like the above.... i want only those characters after the 2nd "/" and before the 3rd "/" what formula to use? in the above result, the answer should be - 78445945 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
FIND 2nd character in a string
The formula was complicated! but it worked!
Thank you very much. "Jacob Skaria" wrote: You can select the column..Data Text to Columns and separate the pieces to columns....If you are looking for a formula below is one way.. =TRIM(LEFT(SUBSTITUTE(MID(A1,FIND("/",A1,FIND("/",A1)+1)+1,LEN(A1)),"/",REPT(CHAR(32),LEN(A1))),LEN(A1))) If this post helps click Yes --------------- Jacob Skaria "Fuzzy" wrote: how do i use the FIND function to get the 2nd character? for eg: A1 has 005/0101/78445945/2002 the entire column has strings like the above.... i want only those characters after the 2nd "/" and before the 3rd "/" what formula to use? in the above result, the answer should be - 78445945 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
find a character in a string | Excel Discussion (Misc queries) | |||
find a character location in a string starting from the right | Excel Worksheet Functions | |||
Excel-Match 1st text character in a string to a known character? | Excel Worksheet Functions | |||
Find last occurance of character in text string | Excel Worksheet Functions | |||
Find nth instance of a character in a string | Excel Discussion (Misc queries) |