Use a formula to set delimiter in text to columns?

[griffo]

Hi there

I'm using text to columns to split some data. My delimiter is a comma, but the problem is that there may be a random comma in the string that I don't want to use as a delimiter (ie it's not consecutive).

There is a pattern to what I want to use as the delimiter - it is number,space - eg 2,_ where the underscore is a space.

I have a basic recorded macro that this is a part of, so I'm guessing if this is possible it would involve changing the "Other:=False" part of the recorded code relating to the TextToColumns but I have no idea what to replace the False with.

Have tried searching the forum but wasn't able to find anything.

Cheers
Griffo

 

[ZAX]

This will assign the character number of this comma and msgbox it:

Sub FindSpecificComma()
Dim Comma
Comma = InStr(1, Replace(ActiveCell.Value, ", ", "!@#$"), "!@#$")
MsgBox Mid(ActiveCell.Value, Comma - 1, 3)
End Sub

ZAX