Function GetChars(target As Range)
Dim MyStr As String, i As Integer
MyStr = ""
If Len(target.Value) = 0 Then GoTo GoExit
For i = 1 To Len(target.Value)
If Not IsNumeric(Mid(target, i, 1)) Then MyStr = MyStr & Mid(target, i, 1)
Next i
GoExit:
GetChars = Trim(MyStr)
End Function
Function GetNums(target As Range)
Dim MyStr As String, i As Integer
MyStr = ""
If Len(target.Value) = 0 Then GoTo GoExit
For i = 1 To Len(target.Value)
If IsNumeric(Mid(target, i, 1)) Or Asc(Mid(target, i, 1)) = 32 Then MyStr = MyStr & Mid(target, i, 1)
Next i
GoExit:
GetNums = MyStr
End Function
10 1098 AD AAA BBB Retreat BP
10 1220 AD Abc Def JAF
10 1230 AD Abc Def SSSC
10 5200 360 1647 9999 100 3 AD 401 Xyz Abc Def JAF
10 5200 360 1731 9999 120 3 AD 301 Ghi Jkl Mno ETRC
10 5200 360 1732 9999 180 3 AD 302 Pqr Stu Vwxyz CASC
10 5200 360 1732 9999 211 3 AD 302 Abc Def Hijkl BP
10 8910 304 4002 9999 110 3 FA CP GED Dallas
10 8910 350 1499 9999 100 3 FA ABCD MBR JAF
Function GetChars(target As Range)
Dim myStr As String, myChar As String, i As Integer
If Len(target.Value) > 0 Then
For i = 1 To Len(target.Value)
myChar = Mid(target, i, 1)
If Not IsNumeric(myChar) And myChar <> " " Then Exit For
Next i
myStr = Mid(target, i)
End If
GetChars = Trim(myStr)
End Function
Function GetNums(target As Range)
Dim myStr As String, myChar As String, i As Integer
If Len(target.Value) > 0 Then
For i = 1 To Len(target.Value)
myChar = Mid(target, i, 1)
If Not IsNumeric(myChar) And myChar <> " " Then Exit For
Next i
myStr = Mid(target, 1, i - 1)
End If
GetNums = Trim(myStr)
End Function
Ron
If you are experiencing a similar issue, please ask a related question
Join the community of 500,000 technology professionals and ask your questions.