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

#### davlow06

##### New Member
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>

### Excel Facts

To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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.)

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

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

Dom

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

Dom

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

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

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!

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.

Replies
18
Views
331
Replies
3
Views
280
Replies
3
Views
142
Replies
5
Views
397
Replies
8
Views
421

1,206,833
Messages
6,075,128
Members
446,123
Latest member
junkyardforme

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back