Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old August 31st 09, 10:31 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2007
Posts: 35
Default 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   Report Post  
Old August 31st 09, 11:48 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2009
Posts: 8,521
Default 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   Report Post  
Old August 31st 09, 11:59 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Jul 2006
Posts: 5,651
Default 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   Report Post  
Old August 31st 09, 03:45 PM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Nov 2008
Posts: 3,346
Default 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   Report Post  
Old September 1st 09, 05:17 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Aug 2009
Posts: 55
Default 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   Report Post  
Old September 1st 09, 05:44 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2007
Posts: 35
Default 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   Report Post  
Old September 1st 09, 06:02 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2007
Posts: 35
Default 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   Report Post  
Old September 1st 09, 08:14 AM posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
First recorded activity by ExcelBanter: Sep 2007
Posts: 35
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


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
Excel-Match 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


All times are GMT +1. The time now is 07:27 PM.

Powered by vBulletin® Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
Copyright 2004-2017 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017