help to a very special split

[RB Smissaert]

That would be something like this:

Sub SplitCell()

Dim arr
Dim LR As Long
Dim r As Long
Dim i As Long

LR = Cells(65536, 1).End(xlUp).Row

For r = 1 To LR
arr = SplitOnNumbers(Cells(r, 1))
For i = 1 To UBound(arr)
Cells(r, 1).Offset(0, i) = arr(i)
Next
Next

End Sub


RBS

 

[Tom Ogilvy]

With your data starting in A1, this worked for all the strings you have
shown:

Kirketorvet 10 Tranely 8310 Tranbjerg
Forteleddet 27 Forteleddet 8240 Risskov
Kirketorvet 10 Tranely 8310 Tranbjerg
Jellebakken 10 Væksthuset 8240 Risskov
Marselis Boulevard 48 Marselis Boulevard 8000 Århus C

It may not look complex, but it seems to get the job done.



Sub SplitData()
Dim bNum As Boolean
Dim s As String, i As Long
Dim rng As Range, cell As Range
Dim sChr As String, sChr1 As String
Set rng = Range(Range("A1"), Range("A1").End(xlDown))
bNum = True
For Each cell In rng
s = Application.Trim(cell.Text)
For i = 1 To Len(s) - 1
sChr = Mid(s, i, 1)
sChr1 = Mid(s, i + 1, 1)
If sChr = " " Then
If bNum Then
If IsNumeric(sChr1) Then
Mid(s, i, 1) = "|"
bNum = False
End If
Else
Mid(s, i, 1) = "|"
bNum = True
End If
End If
Next i
cell.Offset(0, 1).Resize(1, 5).Value = Split(s, "|")
Next cell
End Sub

 

[Guest]

Hi Tom,

<this would seem to be at least as robust for the postulated string and
probably easier to follow>

That said, and while I agree in the context of using just a single
procedure, I was offering reusable "drop-in" functions for filtering text
and/or numbers based on user criteria. Obviously, the OP has posted different
requirements than in the original, so these functions and the procedure using
them are mute here. Your subsequent post has a better solution!

I do thank you for your suggestion about using
Application.International(xlDecimalSeparator)
instead of hard-coding the local one. I'll revise my function respectively.

Regards,
Garry