Assumes the divider as an Ampersand:

Sub EFG()
Dim s As String, s1 As String, s2 As String
Dim iloc As Long
s = ActiveCell.Value
iloc = InStr(1, s, "&", vbTextCompare)
If iloc < 0 Then
s1 = Trim(Left(s, iloc - 1))
s2 = Trim(Right(s, Len(s) - iloc))
ActiveCell.Offset(0, 1) = s1
ActiveCell.Offset(0, 2) = s2
Else
ActiveCell.Offset(0, 1).Value = s
End If
End Sub


--
Regards,
Tom Ogilvy




"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.