Extracting text from a cell in Excel

Question

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?

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

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