I got a problem like, i got a string like:

InText = "TEST RETAIL 00001 Default"

I want the result like this:

L = TEST RETAIL

N= 00001

R = Default

By the below procedure i'm getting the result like:

L = TEST RETAIL

N = 1

R = 000 Default

Public Sub TrippleAtNumber(InText As String, L As String, N As String, R As String)

Dim i As Long

For i = 1 To Len(InText)

'If Val(Mid(InText, i)) > 0 Or Mid(InText, i, 1) = "0" Then

If Val(Mid(InText, i)) > "0" Or Mid(InText, i, 1) = "0" Then

L = Trim(Left(InText, i))

N = Val(Mid(InText, i))

R = Trim(Mid(InText, i + Len(Str(Val(Mid(InText, i))))))

Exit Sub

End If

Next

End Sub

It works fine for other strings but only if in the middle

starts with 0(zero) it gives problem. How to get rid of the problem.

VM

Public Sub TrippleAtNumber()

Dim i As Long

InText = "TEST RETAIL 00001 Default"

For i = 1 To Len(InText)

'If Val(Mid(InText, i)) > 0 Or Mid(InText, i, 1) = "0" Then

If Val(Mid(InText, i)) > "0" Or Mid(InText, i, 1) = "0" Then

L = Trim(Left(InText, i))

pos = InStr(1, (Mid(InText, i + 1)), " ", vbTextCompare)

N = Trim(Mid(InText, i, pos))

R = Trim(Right(InText, Len(InText) - i - pos))

Exit Sub

End If

Next

End Sub