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
AndreamaryAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

[ fanpages ]IT Services ConsultantCommented:
Hi Andrea,

I am not sure your first set of requirements in the question are correct:

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 <- Should this be XX-XXX ?
B4: X <- Should this be XX-X ?

However, reading the requirements within the attached file, "Trim-EE.xls", I suggest you place the following formula in cell [B2], or [C2] (or any column in row 2):

=MID(A2,FIND("-",A2&"-")+1,LEN(A2))

Then copy this down the column to the extent of your data.

I have updated your workbook, as attached.
Q-28688136.xlsx
0
Wayne Taylor (webtubbs)Commented:
You need the MID function to get a substring. The num_chars argument is set to an obligatory 9999 to ensure it definitely reaches the end of the string...

    =IFERROR(MID(A1, FIND("-", A1)+1, 9999), "")

Wayne
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SteveCommented:
I would try:

=SUBSTITUTE(A2,LEFT(A2,FIND("-",A2)),"")

This will just knock off whatever is up to the first "-".

In case of errors:
=IFERRROR(SUBSTITUTE(A2,LEFT(A2,FIND("-",A2)),""),"")
0
AndreamaryAuthor Commented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.