# Excel question: Separating cell contents into separate cells

**URL:** <https://boards.straightdope.com/t/excel-question-separating-cell-contents-into-separate-cells/508341>\
**Category:** Factual Questions\
**Created:** [August 31, 2009, 12:56pm UTC](https://boards.straightdope.com/t/excel-question-separating-cell-contents-into-separate-cells/508341 "2009-08-31T12:56:00Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![jayjay](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/jayjay/32/6765_2.png) [@jayjay](https://boards.straightdope.com/u/jayjay)\
**Post date:** [August 31, 2009, 12:56pm UTC](https://boards.straightdope.com/t/excel-question-separating-cell-contents-into-separate-cells/508341/1 "2009-08-31T12:56:00Z")

</div>

I don’t even know if this is possible, but if it is, it would simplify things for me immensely.

Say I have a class of identifying numbers, consisting of a caseload number connected to a worker number by a hyphen/dash. I want to separate the caseload number and the worker number out so that I can sort by either one. Is there any way to do this quickly and (preferably) automatically? I have tried using find/replace to change the hyphens into spaces, but after that I’m stuck. How do you separate two sets of numbers from sharing space in one cell to having their own cells?

---

<div class="post-metadata">

**Author:** ![Telemark](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/telemark/32/372_2.png) [@Telemark](https://boards.straightdope.com/u/Telemark)\
**Post date:** [August 31, 2009, 1:00pm UTC](https://boards.straightdope.com/t/excel-question-separating-cell-contents-into-separate-cells/508341/2 "2009-08-31T13:00:52Z")

</div>

Go to the Data tab, then Text to Columns. You should be able to split any column of data using the hyphen as the delimiter.

---

<div class="post-metadata">

**Author:** ![Giles](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/giles/32/60_2.png) [@Giles](https://boards.straightdope.com/u/Giles)\
**Post date:** [August 31, 2009, 1:04pm UTC](https://boards.straightdope.com/t/excel-question-separating-cell-contents-into-separate-cells/508341/3 "2009-08-31T13:04:36Z")

</div>

I’m assuming that you have a column containing the cells with a hyphen, and a blank column for the second group of cells. (If you don’t have a blank column, then create one).

The easiest way might be to copy the column with the cells into the blank column, then:  
(1) Select the first column, and use Replace, replacing “-_" with a blank.  
(2) Select the second column, and use Replace, replacing "_-” with a blank.

The “\*” is a wildcard, that matches and group of characters.

The only problem with this method is if your numbers have leading zeroes, and your want to retain them. For example, if a cell has “001-002”, then this method will give you “1” in the first cell and “2” in the second cell. There is a way to retain leading zeroes, but it’s more complex than the method I’ve given.

---

<div class="post-metadata">

**Author:** ![Giles](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/giles/32/60_2.png) [@Giles](https://boards.straightdope.com/u/Giles)\
**Post date:** [August 31, 2009, 1:07pm UTC](https://boards.straightdope.com/t/excel-question-separating-cell-contents-into-separate-cells/508341/4 "2009-08-31T13:07:59Z")

</div>

Telemark’s method works too: I had never used that command, and I suspect it might be useful to me from time to time. In that method, you can preserve leading zeroes by specifying that the columns containing numbers with leading zeroes are “Text” and not “General”.

---

<div class="post-metadata">

**Author:** ![jayjay](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/jayjay/32/6765_2.png) [@jayjay](https://boards.straightdope.com/u/jayjay)\
**Post date:** [August 31, 2009, 1:21pm UTC](https://boards.straightdope.com/t/excel-question-separating-cell-contents-into-separate-cells/508341/5 "2009-08-31T13:21:33Z")

</div>

Text to Columns worked perfectly! Thank you both!

---

<div class="post-metadata">

**Author:** ![gigi](https://avatars.discourse-cdn.com/v4/letter/g/a587f6/32.png) [@gigi](https://boards.straightdope.com/u/gigi)\
**Post date:** [August 31, 2009, 8:37pm UTC](https://boards.straightdope.com/t/excel-question-separating-cell-contents-into-separate-cells/508341/6 "2009-08-31T20:37:39Z")

</div>

Ah, Text to Columns, my dear friend.