Home 
Search 
Today's Posts 
#1




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




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




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/Texttocolumns wizard with "/" as the delimiter, and use the third column. ron 
#4




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




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




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




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




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  
ExcelMatch 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) 