A brother helped me with the below elsewhere to get the above Job done, where as I have to select 6 cells and hit ctrl + Shift + Enter (which makes it an Array formula). I am unable to get in touch with his as he is offline.
The formula is as follows, could someone teach me how to remodel the same to get the following result?
A1=Contact Person: James Anderson Designation: Proprietor >>> to B1=Contact Person, C1=James Anderson, D1=Designation, E1=Proprietor
Function BreakOut(s As String) As Variant
Dim v As Variant
Dim aOut As Variant
Dim i As Long
Dim s1 As String
s1 = s
s1 = Replace(s1, ":", vbNullString)
If s1 Like "*[0-9], [0-9]*" Then
s1 = Replace(s1, ", ", Chr(1))
v = Split(s1, " ")
ReDim aOut(0 To 5)
For i = LBound(aOut) To UBound(aOut)
aOut(i) = vbNullString
For i = LBound(v) To UBound(v)
v(i) = Replace(v(i), Chr(i), ", ")
If v(i) = "-" Then
aOut(i) = "N/A"
ElseIf Right(v(i), 1) = "," Then
aOut(i) = Left(v(i), Len(v(i)) - 1)
aOut(i) = v(i)
BreakOut = aOut
Formatting tags added by mark007
How should I remodel the above?