Using Join and Split

[corky_guy]

Hi - me again.

I need to break up the components an inputted value into their
individual numbers and then place them in other spots in the
document. I'm assuming that I need to use the 'split' and 'join'
commands, but I haven't been able to get them work (especially with
the "/", ".", and ":" in the same line!)

Essentially, users will enter data in the following format:

s2/0/0.1/3/7/1:0

I need to take the individual components of the string:

2/0/0
3
7
1
0

and put them back together and arrange them in a format similar to
this:

controller SONET 2/0/0
!
tug-1 3
7 ch 1
channel-group 0

So, I need to create an array, split the string, and then rejoin the
string? Am I on the right track? Can someone help with the code?

Thanks everyone.

 

[Russ]

Don't try to force everything into one line of code.
You can only use one delimiter in one split function at a time.
You could use the replace function and the split function.
Yes, could nest them into each other, but it would be hard to read what's
going on later.
Pseudo code logic:
Replace "s" with ""
Replace "." with "/"
Replace ":" with "/"
Split using "/"
Concatenate various elements of resultant array with the ampersand character
& to get your required output

 

[Russ]

Don't try to force everything into one line of code.
You can only use one delimiter in one split function at a time.
You could use the replace function and the split function.
Yes, could nest them into each other, but it would be hard to read what's
going on later.
Pseudo code logic:
Replace "s" with ""

If you needed that first letter you could use:
Replace "s" with "s/"

 

[corky_guy]

Ahhh -- yes -- that's a brilliant idea, Russ. How do I replace?

Thanks!

 

[Jonathan West]

Ahhh -- yes -- that's a brilliant idea, Russ. How do I replace?

There is a Replace function in VBA. Look it up in the VBA Help.

 

[corky_guy]

Thanks guys. I'll look up the feature in help. One more thing. I
have some sort of type mismatch in my variables. But, I cannot see
what's wrong.

Private Sub CommandButton1_Click()
Dim port As String
Dim portarray As Variant


port = TextBox8
portarray = Split(port, "/")
port = Join(portarray(1) & portarray(2) & portarray(3), "/")

I keep getting a type mismatch error. Am I storing the variables
incorrectly?

 

[Helmut Weber]

Hi,

port = TextBox8.text

Don't know, what you want to do,
but note that split returns a 0 based array.


--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

 

[corky_guy]

Hi Helmut -

Same result if adding '.text'. I still receive a type mismatch error
and the following line is highlighted:

port = Join(portarray(1) & portarray(2) & portarray(3), "/")

 

[Jay Freedman]

The problem is the syntax you're trying to use in the Join function.
Look at the help topic (in the VBA editor, put the cursor on the word
Join and press F1) for the correct syntax -- the first argument must
be the name of the entire array you want to join, _not_ the string you
get by using the & operator to put the pieces together. In this case,

port = Join(portarray, "/")

Since the code you originally showed, if corrected this way, would
just give back the starting value of the port variable, I assume you
have something else in mind. Do you want to get back the first three
parts of the starting string and discard any others that might be
there? There are a couple of ways to do that:

- Make another Variant variable, copy the first three parts of
portarray into it, and use Join to put them together. Note that this
method, like the one you showed, needs to verify that there are at
least three parts in the starting string, otherwise the macro will
stop with an error.

- Forget about Split and Join. Instead, use several applications of
Instr() to find the location of the third slash (if there is one),
then take the Left() of the original string up to but not including
that position.

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the
newsgroup so all may benefit.

 

[Russ]

Corky,
If you look up the functions in help, they will tell you what they expect.
One quick way to do that, is to put the blinking cursor in a word and press
the F1 function key. If it is a word that belongs to VBA, then the help page
will come up.

Thanks guys. I'll look up the feature in help. One more thing. I
have some sort of type mismatch in my variables. But, I cannot see
what's wrong.

Private Sub CommandButton1_Click()
Dim port As String
Dim portarray As Variant


port = TextBox8

TextBox8 should probably be TextBox8.Value
I recommend that you use
Option Explicit
In the (Declarations) section at the top of module and form code; because it
warns you when a variable is not declared.
Temporarily use
MsgBox port
To test for expected value

portarray = Split(port, "/")
port = Join(portarray(1) & portarray(2) & portarray(3), "/")

The Join function wants to work on a string array.
When you use the &, you are concatenating strings to make a longer string.
The Join function wants a string *array*, not just a longer string.
To use Join, you would have to create another array with the elements from
portarray that you want.
It would be easier to concatenate strings instead like:
port = portarray(1) & "/" & portarray(2) & "/" & portarray(3)
MsgBox port
You can also use CStr() function to convert elements that contain Numbers
into Strings if the & can't handle the concatenation. But it usually does a
good job of forcing the conversion by itself.

 

[Russ]

Corky,
Also if you look up Array in help you'll find that the first element is in
element index (0), unless you use other means to load or specify what the
first array element index should be.

 

[corky_guy]

Thanks for all of the help guys. I have successfully finished part of
what I am trying to accomplish, but now I've run into another problem
that I've been unable to solve today.

Users are going to enter the text in any one of the following methods
(and maybe even more):

Example 1: Serial2/0/0/3/7/1:0
Example 2: Serial2/0/3/7/1:0
Example 3: Serial2/0/0.1/3/7/1:0
Example 4: Serial2/0.1/3/7/1:0

What makes this even more difficult is that the ".1" is irrelevant for
the split but needed for the end result (the end result being
something like this: Serial2/0.1/3/7/1:0).
What will always be true is that I will always need the three digits
to the left of the colon.

So, in example 1, I need to arrive with:
"2/0/0" together in one part of the document and "3", "7", and "1" in
another part.

But, in example 4, I need to arrive with:
"2/0" together in one part of the document and "3", "7", and "1" in
another part.

I can't figure out the way to break up and recombine the data no
matter how the user enters it. Any suggestions?

 

[Russ]

Like a detective looking for clues and testing the evidence.

You can use split via /, like before, to put data into an array.
Ubound(whatever_you_named_array) tells you the highest index number.(upper
boundary)
If its 0-5 elements you handle it one way, 0-6 the other way, not one of
those two, then pop up an error message explaining what might be wrong and
put focus back into the entry object.

If 2nd element is 1 character you handle it one way, if 3 characters the
other way, etc.

If you want to work backwards:
whatever_you_named_array(Ubound(whatever_you_named_array))
the last element will always be number like 1:0
whatever_you_named_array(Ubound(whatever_you_named_array) - 1)
is the next to the last element, etc.

 

[corky_guy]

Thanks, Russ! Using your tips -- and the help from everyone else that
posted in here -- I figured it out!!! Thanks again!

 

[Russ]

You're welcome.
There are usually more than one way to solve a programming situation. Like
when first solving a crossword puzzle, you are stuck at the beginning with
what you know now.
But these forums can show you more choices like a crossword dictionary can
suggest more words.
Good Luck.