I have address information obtained from a 3rd party. All information for a
particular adress is contained in a single cell with no delimiters between
address1 address 2 suburb state and postal code. I can't use fixed width in
text to columns as all components of the address are different character
lengths.

Anyone got any suggestions?

Mike

This is the best I can think of for the moment.

Sub t()
Dim rsp, c, sep As String, sp 'As String
Dim p As Integer, q As Integer
rsp = InputBox("Enter the Number of words before the separator", "Insert
commas in text", 3, 100, 100)

For Each c In Selection
Count = 0
For i = 1 To Len(c)
sp = Mid(c, i, 1)
If sp = " " Then
Count = Count + 1
If CCur(Count) = CCur(rsp) Then
c.Value = Application.WorksheetFunction.Substitute(c, sp, ", ", rsp)
End If
End If
Next i
Next
End Sub

Copy code into a vb module, select the addresses and run the code as many
times as you need.

The code will insert a comma into the 3rd space (after the third word by
default). You will have to re-run the code as often as it takes and be
careful that the data selected needs a coma after the same word on the line
above.

Try spliting up the data into like groups before running the code.

Regards
Peter
"Mike" wrote:

I have address information obtained from a 3rd party. All information for a
particular adress is contained in a single cell with no delimiters between
address1 address 2 suburb state and postal code. I can't use fixed width in
text to columns as all components of the address are different character
lengths.

Anyone got any suggestions?

Mike

Then you can use text to columns

Peter

"Mike" wrote:

I have address information obtained from a 3rd party. All information for a
particular adress is contained in a single cell with no delimiters between
address1 address 2 suburb state and postal code. I can't use fixed width in
text to columns as all components of the address are different character
lengths.

Anyone got any suggestions?