A Microsoft Excel forum. ExcelBanter

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.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

FIND 2nd character in a string



 
 
Thread Tools Display Modes
  #1  
Old August 31st 09, 10:31 AM posted to microsoft.public.excel.worksheet.functions
Fuzzy
external usenet poster
 
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
Ads
  #2  
Old August 31st 09, 11:48 AM posted to microsoft.public.excel.worksheet.functions
Jacob Skaria
external usenet poster
 
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  
Old August 31st 09, 11:59 AM posted to microsoft.public.excel.worksheet.functions
Ron Rosenfeld
external usenet poster
 
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  
Old August 31st 09, 03:45 PM posted to microsoft.public.excel.worksheet.functions
Shane Devenshire[_2_]
external usenet poster
 
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  
Old September 1st 09, 05:17 AM posted to microsoft.public.excel.worksheet.functions
pshepard[_2_]
external usenet poster
 
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  
Old September 1st 09, 05:44 AM posted to microsoft.public.excel.worksheet.functions
Fuzzy
external usenet poster
 
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  
Old September 1st 09, 06:02 AM posted to microsoft.public.excel.worksheet.functions
Fuzzy
external usenet poster
 
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  
Old September 1st 09, 08:14 AM posted to microsoft.public.excel.worksheet.functions
Fuzzy
external usenet poster
 
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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

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 12:18 AM
Excel-Match 1st text character in a string to a known character? bushlite Excel Worksheet Functions 2 January 15th 07 06:36 PM
Find last occurance of character in text string JDay01 Excel Worksheet Functions 2 February 14th 06 04:29 PM
Find nth instance of a character in a string Francis Hayes (The Excel Addict) Excel Discussion (Misc queries) 7 January 21st 05 03:44 PM


All times are GMT +1. The time now is 03:08 AM.


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