Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a long list of items in Column A that includes a code and descriptive text. I need to separate the code from the text description.

 <ABC,1122, A1> - This is the text description

The formatting is always the same, except for the number of letters in the code, so I can't simply use the LEN function. I'm trying to put the code (in brackets) into column B and the text "This is..." in column C.

Any ideas on how I can go about separating this long list?

share|improve this question
up vote 4 down vote accepted

How about:

=Trim(Mid(cellref, 1, Len(cellref) - Search("-",cellref)-2)

As long as the formatting is the same (which you said it is), this should work fine.

share|improve this answer
This gave me the code part. However, it inspired me to look at something else and I ended up using: =RIGHT(cellref,LEN(cellref)-SEARCH("-",cellref,1)). It works fine with another column next to it to TRIM some loose spaces. Thanks for you help. – RocketGoal May 24 '11 at 10:46
As an FYI, you don't need to specify the 'start' value for the SEARCH function. You could just do SEARCH("-",cellref) – wigf1 May 24 '11 at 10:53

In Excel VBA, use InStr() to find the dash "-" position, then use that position information in Left() and Right() to extract the two parts of the string. Reading column A into an array of strings first will possibly speed things up.

share|improve this answer
Unable to find InStr as a formula, so wasn't able to test your suggestion. – RocketGoal May 24 '11 at 10:46
@Mike It's VBA, sorry I didn't specify. – jonsca May 24 '11 at 10:47

To get the description:


To extract the code part:


where A1 contains your string. Wrap them in TRIM to remove leading and trailing spaces.

share|improve this answer

Couldn't you just use the "Text-to-columns" feature under "Data", and split it using the "-" as your delimiter?

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .