If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. 


Thread Tools  Display Modes 
#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 
Ads 
#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 
Thread Tools  
Display Modes  


Similar Threads  
Thread  Thread Starter  Forum  Replies  Last Post 
find a character in a string  kevcar40  Excel Discussion (Misc queries)  4  June 5th 07 12:10 PM 
find a character location in a string starting from the right  funtipoom  Excel Worksheet Functions  2  February 15th 07 01:18 AM 
ExcelMatch 1st text character in a string to a known character?  bushlite  Excel Worksheet Functions  2  January 15th 07 07:36 PM 
Find last occurance of character in text string  JDay01  Excel Worksheet Functions  2  February 14th 06 05:29 PM 
Find nth instance of a character in a string  Francis Hayes (The Excel Addict)  Excel Discussion (Misc queries)  7  January 21st 05 04:44 PM 