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

Answer 1

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

Answer 2

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

Answer 3

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