Hello
I was wondering if anyone knows of a macro that will split the cells in
excel. I have a cell with 6 numbersm then a dash, then words and would like
to split it into two cells, one witht the numbers and one with the words. Any
ideas? Thanks a lot!

You can only split the cell if it was previously merged.




" wrote:

Hello
I was wondering if anyone knows of a macro that will split the cells in
excel. I have a cell with 6 numbersm then a dash, then words and would like
to split it into two cells, one witht the numbers and one with the words. Any
ideas? Thanks a lot!

Record yourself doing DataText to ColumnsDe-limited by a hyphen.


Gord Dibben MS Excel MVP

On Mon, 16 Feb 2009 13:01:01 -0800,
m wrote:

Hello
I was wondering if anyone knows of a macro that will split the cells in
excel. I have a cell with 6 numbersm then a dash, then words and would like
to split it into two cells, one witht the numbers and one with the words. Any
ideas? Thanks a lot!

I'm not sure what you mean by "6 numbers", but let's cell the contents of
each cell in Column A looks like this example (assumed to be in cell A1):

123456-A bunch of words after the dash

In cell B1, you enter the formula "=LEFT(A1,FIND("-",A1)-1)"

In cell C1, you enter the formula "=RIGHT(A1,LEN(A1)-FIND("-",A1))"

These formulas take everything to the left of the dash and put it into one
cell, and everything to the right of the dash and put it in the next cell,
effectively "splitting" the contents of the cell. The dash is left out.

You can then copy cells B1 and C1 to the clipboard, select all cells that
you want to split, and select "Edit/Paste Special..." from the menu.
Finally, select "Formulas", and you will have split all the cells.

You don't need a macro to do it.

Now, if you're really trying to split cells, see the first response!

HTH,

Eric

here is the onsheet formula
123456 excel is easy 123456-excel is easy

C7 = 123456-excel is easy
A7 = =LEFT(C7,6)
B7 = =MID(C7,8,LEN(C7)-6)







" wrote:

Hello
I was wondering if anyone knows of a macro that will split the cells in
excel. I have a cell with 6 numbersm then a dash, then words and would like
to split it into two cells, one witht the numbers and one with the words. Any
ideas? Thanks a lot!