A partial solution as your post #20
Function Extract_Txt(txtrng As String) As String
Dim WrdArray() As String
Dim text_string As String
Dim prefix As String
Dim constr As String
Application.Volatile
text_string = txtrng
text_string = Replace(text_string, "]", "],")
text_string = Replace(text_string, "^", "")
text_string = Replace(text_string, "?", "")
text_string = Replace(text_string, " ", "")
text_string = Replace(text_string, "$", "")
text_string = Replace(text_string, "+", "")
WrdArray() = Split(text_string, ",")
constr = ""
prefix = ""
extractCapx = ""
For i = 0 To UBound(WrdArray)
found = False
n = InStr(1, WrdArray(i), "[")
If n = 0 Then GoTo notfound:
If n <> 1 Then
extractCapx = extractCapx & Left(WrdArray(i), n - 1)
WrdArray(i) = Trim(Mid(WrdArray(i), n, 255))
End If
For j = 1 To Len(WrdArray(i))
n = Asc(Mid(WrdArray(i), j, 1))
If (n >= 65 And n <= 90) Or n = 32 Then
extractCapx = extractCapx & Mid(WrdArray(i), j, 1)
found = True
Exit For
End If
Next j
notfound:
If Not found Then
If WrdArray(i) = "[]" Then WrdArray(i) = " "
extractCapx = extractCapx & WrdArray(i)
End If
Next i
'extractCapx = Replace(extractCapx, "[", "")
Extract_Txt = extractCapx
End Function
in column B
=Extract_text(A2)
Bookmarks