asked on

Trim formula based on first dash in text string

Column A has the following formats:
A1: XXXX-XX
A2: XXXX
A3: XXXX-XX-XXX
A4: XXXX-XX-X

I need a formula in Column B that trims Column A after the first dash, so the results of the above rows would be:
B1: XX
B2:
B3: XXX
B4: X

I have been using the following formula to date, which worked as there was always just one dash in the text string, but now that there can be more than one dash, I am not getting the results I need:
=TRIM(RIGHT(SUBSTITUTE(A1,"-",REPT(" ",LEN(A1))),LEN(A1)))

I have attached a sample file. Column C shows the desired results.

Thanks!
Andrea
Trim-EE.xlsx

SOLUTION

[ fanpages ]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Wayne Taylor (webtubbs)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Steve

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Andreamary

ASKER

Thanks for all your responses...much appreciated. All solutions worked with the exception of Steve's 'iferror' formula, but in drilling down realized that it was just due to a spelling issue (an extra 'R' in error) and once that was fixed, it worked too. I awarded the best solution to Wayne as his was the first solution to include the 'iferror' component and it worked immediately.
Cheers,
Andrea