none
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

Answers

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