Here's some logic that follows my suggestion above:
Sub splitstringCPQA()
Dim ws As Worksheet
Set ws = Sheet1
Dim Name, col As String
Dim pos, count As Integer
Dim stringtosplit As String
stringtosplit = "Name: Young_Yamen, BD: 09/21/1960, Sex: M, Insurance: CHIP(U4024563201), Location: Far Rockaway, Type: Radiology - Results Follow-up"
ws.Range("A44") = stringtosplit
count = 0
While Len(stringtosplit) > 0
pos = InStr(stringtosplit, ": ")
stringtosplit = Mid(stringtosplit, pos + 1, Len(stringtosplit) - pos)
count = count + 1
col = "BCDEFG"
If pos < 1 Then GoTo Error
If InStr(stringtosplit, ",") > 0 Then
ws.Range(Mid(col, count, 1) + "45") = Left(stringtosplit, Len(stringtosplit) - (Len(stringtosplit) - InStr(stringtosplit, ",") + 1))
End If
Wend
Error:
ws.Range("G45") = stringtosplit
End
End Sub
Use the debugger and copious Debug.Print statements to see what's going on.