Answered by:
How to find charaters or split out characters from these?

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/GThere 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
Question
Answers

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
 Edited by Hans Vogelaar MVPMVP Thursday, December 13, 2012 12:30 AM to correct mistake
 Marked as answer by Quist ZhangMicrosoft community contributor, Moderator Thursday, December 20, 2012 8:47 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
 Marked as answer by Quist ZhangMicrosoft community contributor, Moderator Thursday, December 20, 2012 8:47 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
 Edited by Hans Vogelaar MVPMVP Thursday, December 13, 2012 12:30 AM to correct mistake
 Marked as answer by Quist ZhangMicrosoft community contributor, Moderator Thursday, December 20, 2012 8:47 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
 Marked as answer by Quist ZhangMicrosoft community contributor, Moderator Thursday, December 20, 2012 8:47 AM