vmandem
asked on
How to parse out the string into three strings in Visual basic
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
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
ASKER
Hes
I'm using VB 5.0 and the reason i'm using the above procedure is because in my strings i will get more spaces in the middle of the string but i'm splitting the string based on the number in the middle.
I consider the left string untill i see the number in the middle.
The procedure works fine for everything but not if the number starts with zero.
Like i said: Test Retail 00001 Default
It works fine when it is: Test Retail 10001 Default
gives, L = Test Retail
N = 10001
R = Default
VM
Please correct my procedure i appreciate your response
I'm using VB 5.0 and the reason i'm using the above procedure is because in my strings i will get more spaces in the middle of the string but i'm splitting the string based on the number in the middle.
I consider the left string untill i see the number in the middle.
The procedure works fine for everything but not if the number starts with zero.
Like i said: Test Retail 00001 Default
It works fine when it is: Test Retail 10001 Default
gives, L = Test Retail
N = 10001
R = Default
VM
Please correct my procedure i appreciate your response
Try changing
If Val(Mid(InText, i)) > "0" Or Mid(InText, i, 1) = "0" Then
to
If Val(Mid(InText, i)) >= 0 Then
If Val(Mid(InText, i)) > "0" Or Mid(InText, i, 1) = "0" Then
to
If Val(Mid(InText, i)) >= 0 Then
ASKER
hes
Then i get
L = "T"
N = "0"
R = "EST 00001 DEFAULT"
How to resolve this
I appreciate your response.
Thanks
VM
Then i get
L = "T"
N = "0"
R = "EST 00001 DEFAULT"
How to resolve this
I appreciate your response.
Thanks
VM
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Try
For i = 1 To Len(InText)
'If Val(Mid(InText, i)) > 0 Or Mid(InText, i, 1) = "0" Then
If IsNumeric(Mid(InText, i, 1)) Then
If CInt(Mid(InText, i, 1)) >= 0 And CInt(Mid(InText, i, 1)) <= 9 Then
L = Trim(Left(InText, i - 1))
N = Mid(InText, i, 5)
R = Trim(Mid(InText, i + 5))
Exit Sub
End If
End If
Next
For i = 1 To Len(InText)
'If Val(Mid(InText, i)) > 0 Or Mid(InText, i, 1) = "0" Then
If IsNumeric(Mid(InText, i, 1)) Then
If CInt(Mid(InText, i, 1)) >= 0 And CInt(Mid(InText, i, 1)) <= 9 Then
L = Trim(Left(InText, i - 1))
N = Mid(InText, i, 5)
R = Trim(Mid(InText, i + 5))
Exit Sub
End If
End If
Next
Actually you can get rid of the second if
For i = 1 To Len(InText)
If IsNumeric(Mid(InText, i, 1)) Then
L = Trim(Left(InText, i - 1))
N = Mid(InText, i, 5)
R = Trim(Mid(InText, i + 5))
Exit Sub
End If
Next
For i = 1 To Len(InText)
If IsNumeric(Mid(InText, i, 1)) Then
L = Trim(Left(InText, i - 1))
N = Mid(InText, i, 5)
R = Trim(Mid(InText, i + 5))
Exit Sub
End If
Next
If you don't know how long the spaces may be but you always have three portions seperated with a numeric value in the middle, this routine should work for you just fine. I am not sure if you're data always follows that format but if so, try this out.
Option Explicit
Public Sub Test()
Dim strTest As String
Dim L As String
Dim N As String
Dim R As String
strTest = "TEST RETAIL 00001 Default"
TrippleAtNumber strTest, L, N, R
Debug.Print L
Debug.Print N
Debug.Print R
End Sub
Public Sub TrippleAtNumber(InText As String, L As String, N As String, R As String)
Dim i As Long
Dim blnFoundNumeric As Boolean
Dim strChar As String
blnFoundNumeric = False
L = ""
N = ""
R = ""
For i = 1 To Len(InText)
strChar = Mid$(InText, i, 1)
If Not IsNumeric(strChar) Then
If Not blnFoundNumeric Then
L = L & strChar
Else
R = R & strChar
End If
Else
N = N & strChar
blnFoundNumeric = True
End If
Next
L = Trim$(L)
N = Trim$(N)
R = Trim$(R)
End Sub
Option Explicit
Public Sub Test()
Dim strTest As String
Dim L As String
Dim N As String
Dim R As String
strTest = "TEST RETAIL 00001 Default"
TrippleAtNumber strTest, L, N, R
Debug.Print L
Debug.Print N
Debug.Print R
End Sub
Public Sub TrippleAtNumber(InText As String, L As String, N As String, R As String)
Dim i As Long
Dim blnFoundNumeric As Boolean
Dim strChar As String
blnFoundNumeric = False
L = ""
N = ""
R = ""
For i = 1 To Len(InText)
strChar = Mid$(InText, i, 1)
If Not IsNumeric(strChar) Then
If Not blnFoundNumeric Then
L = L & strChar
Else
R = R & strChar
End If
Else
N = N & strChar
blnFoundNumeric = True
End If
Next
L = Trim$(L)
N = Trim$(N)
R = Trim$(R)
End Sub
Try the following.
Dim i As Long
For i = 1 To Len(InText)
If (Asc(Mid$(InText, i)) >= 48) And (Asc(Mid$(InText, i)) <= 57) Then ' i.e. it's a number
L = Trim(Left(InText, i - 1))
N = Mid(InText, i, InStr(i, InText, " ") - i)
R = Trim(Mid(InText, i + Len(N)))
' N=CSTR(VAL(N)) ' Uncomment this line if you want the value to be truncated
End If
Next
I think the problem with your original routine was that you were converting the string into a number, which reduced it's size from '000065' down to '65' or whatever.
Dim i As Long
For i = 1 To Len(InText)
If (Asc(Mid$(InText, i)) >= 48) And (Asc(Mid$(InText, i)) <= 57) Then ' i.e. it's a number
L = Trim(Left(InText, i - 1))
N = Mid(InText, i, InStr(i, InText, " ") - i)
R = Trim(Mid(InText, i + Len(N)))
' N=CSTR(VAL(N)) ' Uncomment this line if you want the value to be truncated
End If
Next
I think the problem with your original routine was that you were converting the string into a number, which reduced it's size from '000065' down to '65' or whatever.
or even...
For i = 1 To Len(InText)
If Mid$(InText, i, 1) Like "#" Then
L = Trim(Left(InText, i - 1))
N = Mid(InText, i, InStr(i, InText, " ") - i)
R = Trim(Mid(InText, i + Len(N)))
End If
Next
For i = 1 To Len(InText)
If Mid$(InText, i, 1) Like "#" Then
L = Trim(Left(InText, i - 1))
N = Mid(InText, i, InStr(i, InText, " ") - i)
R = Trim(Mid(InText, i + Len(N)))
End If
Next
ASKER
bruintje
I accept your comment as answer.
Hes
I really appreciate your comments too. It is not that your comments are wrong, but i got solved my problem with bruintje comment.
I really appreciate every one's comment.
I thank all of them for there valuable comments.
I hope we will continue the same in future.
Thanks
VM
I accept your comment as answer.
Hes
I really appreciate your comments too. It is not that your comments are wrong, but i got solved my problem with bruintje comment.
I really appreciate every one's comment.
I thank all of them for there valuable comments.
I hope we will continue the same in future.
Thanks
VM
No problem, glad you got it solved
thanks for the points, have to admit that this thread is full of usefull solutions
ASKER
bruintje
I got a weird problem on the one we were discussing.
I hope you can give me an answer.
I got string like:
TEST RETAIL 00000 Default
If everything is zero's in the middle, it is considering
like this:
str1: TEST RETAIL 0
str2:00000
str3:Default
Everthing is fine but in str1 it is showing zero at the end. I don't no why.
Could you please take a moment to look into that.
VM
I got a weird problem on the one we were discussing.
I hope you can give me an answer.
I got string like:
TEST RETAIL 00000 Default
If everything is zero's in the middle, it is considering
like this:
str1: TEST RETAIL 0
str2:00000
str3:Default
Everthing is fine but in str1 it is showing zero at the end. I don't no why.
Could you please take a moment to look into that.
VM
Dim TempArr
TempArr = Split(InText," ")
L = TempArr(0) & " " & TempArr(1)
N = TempArr(2)
R = TempArr(3)