1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

MID FIND to separate text with /

Discussion in 'Ask an Excel Question' started by Kelli Webb, Jan 13, 2018.

  1. Kelli Webb

    Kelli Webb New Member

    Messages:
    20
    Hi, HNY all,

    I would like help to separate text that contains a "/" and put into a new column.

    In column C I've used =TRIM(PROPER(IFERROR(RIGHT($A2,LEN($A2)-SEARCH("/",$A2,1)),""))) which seems to be working perfectly.

    However, in Column B I would like to get everything to the left of the "/" or text without so every line in column B is populated

    kind regards,

    Kelli

    Attached Files:

    Last edited: Jan 13, 2018
  2. John Jairo V

    John Jairo V Well-Known Member

    Messages:
    410
    Hi, Kelli!

    You could try:
    [B2] : =IFERROR(TRIM(PROPER(LEFT(A2,FIND("/",A2)-1))),"")

    Blessings!
  3. Kelli Webb

    Kelli Webb New Member

    Messages:
    20
    Hi John, Yes that works - but I realised after I posted it that I would like the data to come over that doesn't contain a "/" also. Any thoughts?
  4. John Jairo V

    John Jairo V Well-Known Member

    Messages:
    410
    Sorry... My mistake.

    Use this:
    [B2] : =IFERROR(TRIM(PROPER(LEFT(A2,FIND("/",A2)-1))),A2)

    Blessings!
    Syndp and Thomas Kuriakose like this.
  5. Kelli Webb

    Kelli Webb New Member

    Messages:
    20
    "perfect" Love your work!

    What are your thoughts on the formula I used above?
    It is working, but is there a sequence that should be used when constructing a formula?
  6. John Jairo V

    John Jairo V Well-Known Member

    Messages:
    410
    Is good. Another option could be:
    [C2] : =IFERROR(TRIM(PROPER(MID(A2,1+FIND("/",A2),99))),"")

    Blessings!
    Thomas Kuriakose likes this.
  7. Kelli Webb

    Kelli Webb New Member

    Messages:
    20
    True, am I able to add in a provision to split "-" if used instead of "/"?

    Kelli
  8. S. Das

    S. Das Member

    Messages:
    61
    Kelli, one more option for you
    1) Select the data range (like A2 to A11 in your example)
    2) Goto Data--> Text to Columns
    3) Select Delimited--> then press next
    4) Choose delimiters from the given list (if you want to add any other symbol then choose OTHER and mention the symbol in the box)
    5) Press Next
    6) Choose your Column Data format (By default GENERAL) and write the destination (in your case, write $B$2 or you can write any column you want)
    7) Press FINISH

    Enjoy!!
    Syndp likes this.

Share This Page