Get characters on left of specified character in Excel

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

In an Excel column I am having values like
A1 - abcd - abcdedfg
A2 - abc - zyxwvu
A3 - ab - mnopq

I need a formula to get all characters left of the hypen for each value in
another column. Hypen may occur in any position (except first).

Any pointers? Thanks in Advance
 
Hi

If you are talking about Left of the first hyphen, then
=LEFT(A1,FIND("-",A1)-1)

If you are talking about left of the second hyphen then
=LEFT(A1,FIND("^",SUBSTITUTE(A1,"-","^",2))-1)

If you are talking about between the 2 hyphens then
=MID(A1,FIND("-",A1)+2,FIND("^",SUBSTITUTE(A1,"-","^",2))-FIND("-",A1)-2)
 
Thanks a lot. It really helped.

Roger Govier said:
Hi

If you are talking about Left of the first hyphen, then
=LEFT(A1,FIND("-",A1)-1)

If you are talking about left of the second hyphen then
=LEFT(A1,FIND("^",SUBSTITUTE(A1,"-","^",2))-1)

If you are talking about between the 2 hyphens then
=MID(A1,FIND("-",A1)+2,FIND("^",SUBSTITUTE(A1,"-","^",2))-FIND("-",A1)-2)
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help creating a formula that repeats text & increments an integer 3
Counting Specific Character(s) In A Range? 10
IF a cell only contains a specific character then reformat the tex 3
Copy string of characters 5
Formula to validate characters within a string of data in aparticular cell 1
How to add a space after each text value in all the cells in a row 4
Keep last 4 characters of value 5
How do I get more than 255 characters into a cell? 3

Back
Top