Klaus Busch who is active on the group kindly wrote some code for me
awhile back.

Basically it converts the following data,

4/7 Aspire Tower, 9/2 Cerberus, 13/2 Wolf Prince, 12/1 A Wave Of The
Sea, 16/1 Never Do Nothing, 20/1 Hammersmith, 25/1 Clemencia, Oak Park,
50/1 Takarengo, 100/1 Three Comets.

into this format in seperate cells.

Aspire Tower 4/7
Cerbus 13/2
Wolf Prince 13/2

etc.....

It worked fine, but now I get a syntax error on the following line.

.Range("B2").Resize(UBound(varOut, 2), 2) =

I cannot think why, but I'm not that code savvy.

The full code is below.

Option Explicit

Sub SplitData()
Dim LRow As Long, i As Long, n As Long
Dim j As Integer
Dim varData As Variant, varTmp As Variant, varOut() As Variant

n = 1
With ActiveSheet
LRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("A2:A" & LRow).Replace Chr(160), Chr(32)
varData = .Range("A2:A" & LRow)
For i = LBound(varData) To UBound(varData)
varTmp = Split(varData(i, 1), ",")
For j = LBound(varTmp) To UBound(varTmp)
ReDim Preserve varOut(1 To 2, 1 To n)
If Asc(Left(Trim(varTmp(j)), 1)) < 65 Then
varOut(1, n) = Mid(Trim(varTmp(j)),
InStr(Trim(varTmp(j)), " ") + 1)
varOut(2, n) = CStr(Trim(Replace(varTmp(j), varOut(1,
n), "")))
Else
varOut(1, n) = Trim(varTmp(j))
varOut(2, n) = varOut(2, n - 1)
End If
n = n + 1
Next
Next
.Range("C2").Resize(UBound(varOut, 2)).NumberFormat = "@"
.Range("B2").Resize(UBound(varOut, 2), 2) =
Application.Transpose (varOut)
End With
End Sub

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

Hi,

Am Sun, 2 Feb 2020 13:12:23 +0000 schrieb Saxman:

4/7 Aspire Tower, 9/2 Cerberus, 13/2 Wolf Prince, 12/1 A Wave Of The
Sea, 16/1 Never Do Nothing, 20/1 Hammersmith, 25/1 Clemencia, Oak Park,
50/1 Takarengo, 100/1 Three Comets.

into this format in seperate cells.

Aspire Tower 4/7
Cerbus 13/2
Wolf Prince 13/2

etc.....

perhaps an issue because of word wrap.

Try it again:

Sub SplitData()
Dim LRow As Long, i As Long, n As Long
Dim j As Integer
Dim varData As Variant, varTmp As Variant, varOut() As Variant

n = 1
With ActiveSheet
LRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("A2:A" & LRow).Replace Chr(160), Chr(32)
varData = .Range("A2:A" & LRow)
For i = LBound(varData) To UBound(varData)
varTmp = Split(varData(i, 1), ",")
For j = LBound(varTmp) To UBound(varTmp)
ReDim Preserve varOut(1 To 2, 1 To n)
If Asc(Left(Trim(varTmp(j)), 1)) < 65 Then
varOut(1, n) = Mid(Trim(varTmp(j)), _
InStr(Trim(varTmp(j)), " ") + 1)
varOut(2, n) = CStr(Trim(Replace(varTmp(j), _
varOut(1, n), "")))
Else
varOut(1, n) = Trim(varTmp(j))
varOut(2, n) = varOut(2, n - 1)
End If
n = n + 1
Next
Next
.Range("C2").Resize(UBound(varOut, 2)).NumberFormat = "@"
.Range("B2").Resize(UBound(varOut, 2), 2) = _
Application.Transpose(varOut)
End With
End Sub


Regards
Claus B.
--
Windows10
Office 2016

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On 02/02/2020 16:50, Claus Busch wrote:

Sub SplitData()
Dim LRow As Long, i As Long, n As Long
Dim j As Integer
Dim varData As Variant, varTmp As Variant, varOut() As Variant

n = 1
With ActiveSheet
LRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("A2:A" & LRow).Replace Chr(160), Chr(32)
varData = .Range("A2:A" & LRow)
For i = LBound(varData) To UBound(varData)
varTmp = Split(varData(i, 1), ",")
For j = LBound(varTmp) To UBound(varTmp)
ReDim Preserve varOut(1 To 2, 1 To n)
If Asc(Left(Trim(varTmp(j)), 1)) < 65 Then
varOut(1, n) = Mid(Trim(varTmp(j)), _
InStr(Trim(varTmp(j)), " ") + 1)
varOut(2, n) = CStr(Trim(Replace(varTmp(j), _
varOut(1, n), "")))
Else
varOut(1, n) = Trim(varTmp(j))
varOut(2, n) = varOut(2, n - 1)
End If
n = n + 1
Next
Next
.Range("C2").Resize(UBound(varOut, 2)).NumberFormat = "@"
.Range("B2").Resize(UBound(varOut, 2), 2) = _
Application.Transpose(varOut)
End With
End Sub

That works fine Claus.

Thank you very much.

Enjoy your day.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)