Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# How to parse out the string into three strings in Visual basic

Posted on 2002-03-22
Medium Priority
240 Views
Last Modified: 2013-11-25
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
0
Question by:vmandem
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• Learn & ask questions
• 5
• 4
• 2
• +2
14 Comments

LVL 20

Expert Comment

ID: 6890029
Try this

Dim TempArr

TempArr = Split(InText," ")
L = TempArr(0) & " " & TempArr(1)
N = TempArr(2)
R = TempArr(3)
0

Author Comment

ID: 6890039
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
0

LVL 20

Expert Comment

ID: 6890048
Try changing
If Val(Mid(InText, i)) > "0" Or Mid(InText, i, 1) = "0" Then

to

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

Author Comment

ID: 6890061
hes

Then i get

L = "T"
N = "0"
R = "EST 00001 DEFAULT"

How to resolve this

I appreciate your response.

Thanks
VM
0

LVL 44

Accepted Solution

bruintje earned 60 total points
ID: 6890064
and with

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
0

LVL 20

Expert Comment

ID: 6890093
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
0

LVL 20

Expert Comment

ID: 6890170
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
0

LVL 6

Expert Comment

ID: 6890496
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
0

LVL 10

Expert Comment

ID: 6891038
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.
0

LVL 10

Expert Comment

ID: 6891044
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
0

Author Comment

ID: 6891180
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
0

LVL 20

Expert Comment

ID: 6891183
No problem, glad you got it solved
0

LVL 44

Expert Comment

ID: 6891204
thanks for the points, have to admit that this thread is full of usefull solutions
0

Author Comment

ID: 6894510
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
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library aâ€¦
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that aâ€¦
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlinâ€¦
###### Suggested Courses
Course of the Month4 days, 16 hours left to enroll

#### 670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.