Text to Columns, need help separating column info with many dashes

[davlow06]

I have a spreadsheet that has columns I need to separate the information in, but I can't figure out how to split the information. Here is an example:
1 - 461.9 - ACUTE SINUSITIS NOS - 461.9
2 - V06.1 - DIPHTHERIA-TERN-PERTUSIS,DTP,COMB - V06.1

Two examples of information that is in the columns I'm trying to separate, and there are over 1000 rows full of information that is different like this. I want to split the number code (461.9 or V06.1) from the description after the hyphen. The problem is, in example number 2, notice that there are hyphens between the description. A typical delimited type will separate every hyphen, but I want the description to stay together.

Any help with this?

Thanks!

<tbody></tbody>

 

[pplstuff]

You could try left(a1,4) and right(a1,4) to get each end off.. Then use mid() to capture the middle (which I'm guessing is the description.)

 

[Domski]

I assume it's more complex than: =RIGHT(A1,5)

so maybe: =TRIM(RIGHT(SUBSTITUTE(A1," - ",REPT(" ",50)),50))

Dom

 

[Domski]

Or if I've misunderstood: =LEFT(MID(A1,9,LEN(A1)-9),LEN(A1)-16)

Dom

 

[davlow06]

Where am I supposed to use codes like what you have given? They look like formulas, but how would I place them?

 

[Domski]

Just put them in the next available column and change the reference from A1 to the cell you want the formula to work on.

Dom

 

[davlow06]

Yes, I did that and it works, but only for that specific column. With over 1000 rows, I would have to change the "spaces" in every cell to match the length of the description. See example #1. Using -9 and -16 only get me 'ute sin' in the description portion, so that doesn't work unless I manually change every value in every cell. It is a good start though!

 

[Norie]

Do the hyphens separating the decription and number always have a space around them?

If they do you could use a formula like this to replace them with a semi-colon.

=SUBSTITUTE(A1, " - ", ";")

Then you could do a Copy>Paste Special values to just get the values and then you could do Text to columns... with semi-colon as the delimiter.

Probably a long shot, I doubt it's always a hypen surrounded by spaces.