Hi Don,

You're a genius! Just what I was looking for. Not that I will ever know what
you know, but can you recommend a source (book, website, etc.) to get me
started?

Thanks very much for your help!
Roy

PS I'm intrigued that your method and Tom's method (above) are so
different, but both work well. Is that what they mean when they say "There's
more than one way to skin a cat?"


"Don Guillett" wrote in message
...
You can use datatext to columns to divide and then delete the & column

or

Sub separatecell()
For Each c In Selection
x = InStr(c, "&")
c.Offset(, 1) = Right(c, Len(c) - x)
c.Value = Left(c, x - 2)
Next c
End Sub

--
Don Guillett
SalesAid Software

"C. Roy Blye" wrote in message
t...
Hi,

I would like to write a macro that divides the contents of one cell into
two separate cells.

For example: If a cell contains "cats & dogs", I would like to convert
the contents of the cell into two cells, one containing "cats" and the
other containing "dogs". (The quotes are not part of the actual
contents.)

My feeble attempt to create a macro for this always results in "cats" and
"dogs", even when the original cell contains "horses & cows". In other
words, I need a macro that isn't so "literal".

As you may have guessed, my macro was created by recording it, rather
than writing it from scratch. (I admit to being a novice at this.)

I can see where the problem is in the code, but I don't know how to fix
it. For example, this line from the code:

Range("A2").Select
ActiveCell.FormulaR1C1 = "cats"

needs to say something like:

Range("A2").Select
ActiveCell.FormulaR1C1 = "string to left of & sign"

Thanks in advance for any help.

Roy

PS If there is a more appropriate newsgroup for this question, please let
me know.