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...
 
Back
Top