Get characters on left of specified character in Excel
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 |
Answer: Get characters on left of specified character in Excel
Note: If there is no hyphen in the text string, the formula will return an error. To avoid this, you can use the IFERROR function to display a blank cell instead of an error message. Here's the modified formula: Code:
=IFERROR(LEFT(A1,FIND("-",A1)-1),"") |
Get characters on left of specified character in Excel
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) -- Regards Roger Govier "Murugan" wrote in message ... 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 |
Get characters on left of specified character in Excel
Thanks a lot. It really helped.
"Roger Govier" wrote: 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) -- Regards Roger Govier "Murugan" wrote in message ... 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 |
All times are GMT +1. The time now is 03:45 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com