# How to find charaters or split out characters from these?

### Question

• Hi,

I have data in excel, but I need to split it. The problem is, the data is variable.

I can have something like these: lets say it is in column A (a:1 through a:6 in this case, can be more or less rows)

BC/F
A/BC/G
1A/BC/H
BCDE/I
A/BCDE/J
1A/BCDE/G

There will never be more than 1 charachter to the right of the second"/".  There can be 2,3, or 4 characters between the "/"'s or left of the "/" if there is only one. I want the characters between the two "/" or left of the "/" if there is only one.  How do I do this? The data can go to another cell, say next to it in column B.

Thanks

Wednesday, December 12, 2012 10:36 PM

• In B1 (I mistakenly wrote A1 originally):

=IF(ISERROR(FIND("/",A1,FIND("/",A1)+1)),LEFT(A1,FIND("/",A1)-1),MID(A1,FIND("/",A1)+1,FIND("/",A1,FIND("/",A1)+1)-FIND("/",A1)-1))

Fill down to the end of the data.

Regards, Hans Vogelaar

Wednesday, December 12, 2012 11:13 PM
• Sorry, I should have written "In B1" of course.

For the part to the left of the first slash if there are two, you can use

=IF(ISERROR(FIND("/",A1,FIND("/",A1)+1)),"",LEFT(A1,FIND("/",A1)-1))

Regards, Hans Vogelaar

Thursday, December 13, 2012 12:29 AM

### All replies

• In B1 (I mistakenly wrote A1 originally):

=IF(ISERROR(FIND("/",A1,FIND("/",A1)+1)),LEFT(A1,FIND("/",A1)-1),MID(A1,FIND("/",A1)+1,FIND("/",A1,FIND("/",A1)+1)-FIND("/",A1)-1))

Fill down to the end of the data.

Regards, Hans Vogelaar

Wednesday, December 12, 2012 11:13 PM
• Hans,

Thank You, This was Perfect!  Except I would put this in B1 instead of A1.

After I made the first post, I realized I also need the data left of the first "/" when there are two.  Again, variable original data as posted

Steve

Thursday, December 13, 2012 12:24 AM
• Sorry, I should have written "In B1" of course.

For the part to the left of the first slash if there are two, you can use

=IF(ISERROR(FIND("/",A1,FIND("/",A1)+1)),"",LEFT(A1,FIND("/",A1)-1))

Regards, Hans Vogelaar

Thursday, December 13, 2012 12:29 AM