Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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
add comment

4 Answers

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
1  
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
add comment

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
add comment

To get the description:

=MID(A1,FIND("-",A1,1)+2,LEN(A1))

To extract the code part:

=MID(A1,1,FIND(">",A1,1))

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

share|improve this answer
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.