You can try code like the following. First, select the cells containing the
data to split. The split components will be in the 3 columns to the right of
the original data. When you run the code, you will be prompted for the
length of the first two components to split, separated by a comma. For
example, to split after into 3 characters then 1 characters, enter '3,1'.
The third split text is the remaining text on the right, regardless of its
length.

Sub AAAA()

Dim S As String
Dim N As Long
Dim V As Variant
Dim R As Range
Dim S1 As String
Dim S2 As String
Dim S3 As String

S = InputBox("Enter data widths separated by commas")
If S = vbNullString Then
Exit Sub
End If
S = Replace(S, " ", vbNullString)
V = Split(S, ",")
For N = LBound(V) To UBound(V)
If IsNumeric(V(N)) = False Then
MsgBox "Non numeric entry is invalid"
Exit Sub
End If
Next N
If UBound(V, 1) - LBound(V, 1) + 1 < 2 Then
MsgBox "Invalid data"
Exit Sub
End If
If Not TypeOf Selection Is Excel.Range Then
MsgBox "Selection is not a range"
Exit Sub
End If
If Selection.Columns.Count 1 Then
MsgBox "Selection must be only one column"
Exit Sub
End If
For Each R In Selection.Cells
S1 = vbNullString
S2 = vbNullString
S3 = vbNullString
S1 = Left(R.Text, CInt(V(0)))
S2 = Mid(R.Text, CInt(V(0)) + 1, CInt(V(1)))
S3 = Mid(R.Text, CInt(V(0)) + CInt(V(1)))
R(1, 2) = S1
R(1, 3) = S2
R(1, 4) = S3
Next R
End Sub


--
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)




"SirEric" wrote in message
...
Good Sirs & Madams:

Please help me with my excel question. Thank you in advance.

1002bat
2003cat
3009dog
4005frog
5003snake

I want to take single cell data and split the contents into multiple
cells.

"1002bat" would become "100", "2", "Bat".

The way our information is "imported" into excel results in the
concatenation of data that would be useful to be split.

Is there a macro that can take the first "x" digits/text" of a long string
in order to accomplish this separation.

Thanks,

Sir Eric
(Queen's Knight)