Solved

How to split a string into three strings

Posted on 2002-05-23
22
298 Views
Last Modified: 2010-05-02
I already have a procedure like this:

Public Sub TrippleAtNumber(InText As String, L As String, N As String, R As String)
   Dim i As Long
   Dim pos As Long
   For i = 1 To Len(InText)
 
'Testing

     If Val(Mid(InText, i)) > "0" Or Mid(InText, i, 1) = "0" Then
         L = Trim(Left(InText, i - 1))
         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

'Testing

   Next
End Sub


My string is like this: AMS NSC NCS

I want L: AMS
       N: NSC
       R: NSC

IF MY STRING IS :AMS 123 NCS, MY above procdure splits
correctly and when the middle one is character what i need
to modify in my procedure.

Pleas modify the above procedure.

VM
0
Comment
Question by:vmandem
  • 10
  • 7
  • 2
  • +2
22 Comments
 
LVL 5

Expert Comment

by:bob_online
ID: 7030168
easiest, most efficient thing to do

dim arrText(string)

arrText = Split(InText, " ")
L = arrText(0)
N = arrText(1)
R = arrText(2)

0
 
LVL 18

Expert Comment

by:bobbit31
ID: 7030182
why not use split function?

Dim arrLNR() as String

arrLNR = split (InText)

L = arrLNR(0)
N = arrLNR(1)
R = arrLNR(2)

0
 
LVL 18

Expert Comment

by:bobbit31
ID: 7030183
damn... go off to another thread for a second and someone beats ya too it ;)
0
 

Author Comment

by:vmandem
ID: 7030187
bob

I have problem mine is VB5.0 and i'm using the above function which works similarly to split.

My problem is my string could also be like this:

string = ABS BKS 5609 MCS
MY procedure now splitting the way i want it like:

L = ABS BKS
N = 5609
R = MCS

But if my string is having character in the middle than my
procedure is not splitting the way i want
like
string: ABS MCS SSS

L : ABS
N : MCS
R : SSS

My string could be : ABS BBS MCS SSS then
i want to consider
L:ABS BBS
N:MCS
R:SSS

how to do that using my procedure. Just modify my procedure
that will help me a lot.

Thanks
VM

0
 

Author Comment

by:vmandem
ID: 7030209
bob

I have problem mine is VB5.0 and i'm using the above function which works similarly to split.

My problem is my string could also be like this:

string = ABS BKS 5609 MCS
MY procedure now splitting the way i want it like:

L = ABS BKS
N = 5609
R = MCS

But if my string is having character in the middle than my
procedure is not splitting the way i want
like
string: ABS MCS SSS

L : ABS
N : MCS
R : SSS

My string could be : ABS BBS MCS SSS then
i want to consider
L:ABS BBS
N:MCS
R:SSS

how to do that using my procedure. Just modify my procedure
that will help me a lot.

Thanks
VM

0
 
LVL 18

Expert Comment

by:bobbit31
ID: 7030216
just curious, why are you passing L, N, R to your Sub?
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 7030229
You use split and check if you got 4 strings.

Expanding on Bob_online & bobbit's answer

dim arrText(string)

arrText = Split(InText, " ")

if ubound(arrText) = 4 then
  L = arrText(0) & " " & arrText(1)
  N = arrText(2)
  R = arrText(3)
else
  L = arrText(0)
  N = arrText(1)
  R = arrText(2)
end if

mlmcc
0
 

Author Comment

by:vmandem
ID: 7030239
I'm using VB5.0 so i can't use Split function and it is not
available in VB5.0
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 7030246
Minor error

dim arrText(string)

arrText = Split(InText, " ")

if ubound(arrText) = 3 then
 L = arrText(0) & " " & arrText(1)
 N = arrText(2)
 R = arrText(3)
else
 L = arrText(0)
 N = arrText(1)
 R = arrText(2)
end if

mlmcc
0
 

Author Comment

by:vmandem
ID: 7030248
bob

Because i'm getting the string values after splitting into three strings and i'm using later the three values to insert into my table

VM
0
 

Author Comment

by:vmandem
ID: 7030274
bob

Because i'm getting the string values after splitting into three strings and i'm using later the three values to insert into my table

VM
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 18

Expert Comment

by:bobbit31
ID: 7030284
here's how i would do it:

Public Function TrippleAtNumber(InText As String) As String()
  Dim arrReturn(0 To 2) As String
  Dim i As Long
  Dim pos As Long
 
  numWords = 0
  done = False
 
  While done <> True
    pos = InStr(1, InText, " ", vbTextCompare)
   
    If pos = 0 Then '' we're done
        curWord = InText
        done = True
    Else
        curWord = Left(InText, pos - 1)
    End If
   
    If numWords >= 3 Then
        arrReturn(0) = arrReturn(0) & " " & arrReturn(1)
        arrReturn(1) = arrReturn(2)
        arrReturn(2) = curWord
    Else
        arrReturn(numWords) = curWord
    End If
   
    numWords = numWords + 1
    InText = Right(InText, Len(InText) - pos)
  Wend
 
  TrippleAtNumber = arrReturn


End Function

Private Sub Command1_Click()
Dim arrLNR() As String

arrLNR = TrippleAtNumber("ACS CSCC DMD")

MsgBox arrLNR(0)
MsgBox arrLNR(1)
MsgBox arrLNR(2)

arrLNR = TrippleAtNumber("ACS CSCC 123 DMD")

MsgBox arrLNR(0)
MsgBox arrLNR(1)
MsgBox arrLNR(2)


End Sub
0
 

Author Comment

by:vmandem
ID: 7030328
Bob

I get all typemismatch errors at this place,i don't no
you may be thinking of vb6.0 but im using vb5.0

here it comes:

Dim arrReturn(0 To 2) As String--> Type mismatch

and i can't declare this one as
Public Function TrippleAtNumber(InText As String) As String()

Like: string() at the end it gives me type mismatch errors.

VM
0
 
LVL 18

Expert Comment

by:bobbit31
ID: 7030380
Public Sub TrippleAtNumber(InText As String, L As String, N As String, R As String)
 Dim i As Long
 Dim pos As Long

 numWords = 0
 done = False
 
 While done <> True
   pos = InStr(1, InText, " ", vbTextCompare)
   
   If pos = 0 Then '' we're done
       curWord = InText
       done = True
   Else
       curWord = Left(InText, pos - 1)
   End If
   
    If numWords = 0 Then
        L = curWord
    ElseIf numWords = 1 Then
        N = curWord
    ElseIf numWords = 2 Then
        R = curWord
    ElseIf numWords >= 3 Then
       L = L & " " & N
       N = R
       R = curWord
    End If
   
   numWords = numWords + 1
   InText = Right(InText, Len(InText) - pos)
 Wend
 
 MsgBox L
 MsgBox N
 MsgBox R
 
End Sub

Private Sub Command1_Click()

Dim L As String
Dim N As String
Dim R As String

TrippleAtNumber "ACS CSCC DMD", L, N, R
TrippleAtNumber "ACS CSCC 123 DMD", L, N, R

End Sub
0
 
LVL 1

Expert Comment

by:johnsand
ID: 7030996
This should work...   I don't have VB5 to test it...  This is to emulate the VB6 Split function.  Works fine in VB6... :-)

Use this function and then follow the examples above for SPLIT

Function Split(strValue As String, strDivider As String) As Variant
'strValue is the string you want to split
'strSplitter is the character that divides the values
    Dim arrArray() As String
    Dim nCounter As Integer
    Dim sHold As String
    Dim nSplitCounter As Integer
   
    'Init the Array
    nSplitCounter = 0
    ReDim arrArray(nSplitCounter)
    'Make sure we only use the first char of the divider string
    '(in case they pass more than 1)
    strDivider = Left$(strSplitter, 1)
    'Let's parse each letter and see what we have
    For nCounter = 1 To Len(strValue)
        Select Case Asc(Mid$(strValue, nCounter, 1))
            Case Asc(strDivider)
                'Hey, we found our divider
                'set the current array element to our string so far
                 arrArray(nSplitCounter) = sHold
                 'reset our work string
                 sHold = ""
                 'increase our array by 1
                 nSplitCounter = nSplitCounter + 1
                 ReDim Preserve arrArray(nSplitCounter)
            Case Else
                'not our divider, just add the current character to our work string
                sHold = sHold + Mid$(strValue, nCounter, 1)
        End Select
    Next
    'add our final string to the array
    arrArray(nSplitCounter) = sHold
    'send the array back as the functions return
    Split = arrArray
End Function

'An example call
sub command1_click()
    Dim arrArray() As String
   
    arrArray = Split("This is a test of the splitter", " ")
end sub
0
 
LVL 1

Expert Comment

by:johnsand
ID: 7031000
oops...

replace the following...  I had the wrong value in there

    'Make sure we only use the first char of the divider string
    '(in case they pass more than 1)
    strDivider = Left$(strDivider, 1)
0
 

Author Comment

by:vmandem
ID: 7037138
Bob

I'm going to try yours and let you know. I appreciate your response.

VM

Jhone i'm going to try the split function too and let you know the progress.

VM
0
 

Author Comment

by:vmandem
ID: 7039520
bobbit

Everything looks fine except like this case:

I have string which is :

AEC 5566 AEC TEST TEST1

in the above case i want
L = AEC
N = 5566
R = AEC TEST TEST1

Based on your logic, it is doing:

L = AEC 5566
N = AEC
R = TEST TEST1

For this i tried another elseif like this but didn't worked:

ElseIf numwords > 3 Then
      L = L
      N = R
      R = curword

how to handle such kind of string.
I appreciate your response
VM
0
 
LVL 18

Expert Comment

by:bobbit31
ID: 7039574
  ElseIf numWords >= 3 Then
       If IsNumeric(N) Then '' we've found a number
          R = InText
          done = True
       Else
           L = L & " " & N
           N = R
           R = curWord
       End If
   End If
0
 

Author Comment

by:vmandem
ID: 7039687
Bobbit

I'm sorry not to mention this, in my string i have also
like :

AEC 5566 AEC/TEST TEST1

according to the new code it is taking only like:

L = AEC
N = 5566
R = TEST1 and skipping the AEC/TEST, i want like
R = AEC/TEST TEST1. why is it picking only the last one.

I appreciate your response. I'm going to increase the points too.

VM
0
 
LVL 18

Accepted Solution

by:
bobbit31 earned 50 total points
ID: 7039714
oops:

  ElseIf numWords >= 3 Then
      If IsNumeric(N) Then '' we've found a number
         R = R & " " & InText
         done = True
      Else
          L = L & " " & N
          N = R
          R = curWord
      End If
  End If
0
 

Author Comment

by:vmandem
ID: 7044799
Bobbit
I really appreciate your response. I'm really sorry for
not getting back immediatly. I hope we will continue the
same in future.

I really appreciate other experts comments too.

Thanks
VM

0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

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

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

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now