Split text string into 5 columns

Hello there!

I have spent already quite an amount of hours trying to get a VBA code for an excel Macro.... and it is time to ask for help.

I have a string that will always have 10 characters:

0010412521 - 001 School 04 Lesson 1 Yes 25 No. Girls 21 No. Boys.

I would like a Macro to split this string into 5 columns separated as the example shows.

Since I do not know how to program in VBA I found a Macro I am kindly requesting your help.

If the code could include the fact that I want to run the Macro ONLY on the selected cells it would be perfect.
This is waht I found for that:

Dim SelCell As Range

For Each SelCell In Selection
MsgBox SelCell.Value
Next SelCell

But I cannot figure out how to make it part of the code I need.

Thank you!

you need to use the left, mid and right functions.

As worksheet functions, if your string is in A1, then:
=left(A1,3)
=mid(A1,4,2)
=mid(A1,6,1)
=mid(A1,7,2)
=right(A1,2)
will give you the different numbers required.
You can then drag this down the columns as neccessary.

Alternatively you can do the same thing in vba:

Please Login or Register  to view this content.

Thank you very much for the code!!!
However, I would like it to do it to all the selected cells, because each time it will be a diferent selection.
Is it possible to include such code into this one??
Sub Splitting()
Dim cl
For Each cl In Range("C4:C4")
Range("D4") = Mid(Range("C4"), 1, 3)
Range("E4") = Mid(Range("C4"), 4, 2)
Range("F4") = Mid(Range("C4"), 6, 1)
Range("G4") = Mid(Range("C4"), 7, 2)
Range("H4") = Mid(Range("C4"), 9, 2)
Next cl
End Sub

Where it goes from the first cell selected: C4 to D4 E4 F4 G4 H4
And the next C5 to D5 E5 F5 G5 H5 etc...

Is there like a loop that would do from i to 'n' where the "n" is the number of the rows of the selection?

THANK YOU!

I think:

Please Login or Register  to view this content.

will give you the first row and

Please Login or Register  to view this content.

will give you the last row, so you would want:

Please Login or Register  to view this content.

Yudlugar! Thank you so so so much again. This seemed to have worked once but it did it for the whole excel and it ended up Not responding.
Now, it gives Error '13, where the Debuger says the mistake is on the third line "For count = Selection.Row To Selection.Row + Selection.Rows"
This is the part that does not compile. I tried to change it to
"For count = Selection.Row To Selection.Rows"
But still does not work...
We are amost there, thenk you so much Yudlugar!

Sub splitting()
Dim count
For count = Selection.Row To Selection.Row + Selection.Rows

Range("D" & count) = Mid(Range("C" & count), 1, 3)

Range("E" & count) = Mid(Range("C" & count), 4, 2)

Range("F" & count) = Mid(Range("C" & count), 6, 1)

Range("G" & count) = Mid(Range("C" & count), 7, 2)

Range("H" & count) = Mid(Range("C" & count), 9, 2)

Next
End Sub

FWIW:

Please Login or Register  to view this content.

John - only works if selection starts in row 1 I think, if I select rows 5 to 10 then your for loop becomes:

Please Login or Register  to view this content.

However, the rows.count part is the bit that was missing from my code:

Please Login or Register  to view this content.

Hello John,
I really appreciate your help and this code seems to be exactly what i was looking for, but since I never programmed in Visual Basic I had no clue of how to code it.
I copy pasted it and select the cells from C4 to C6, run the code and.... nothing happens.
I have also tried selecting the WHOLE ROWS in case that was the issue.... nothing happens...
Can you help me?
I am sending you the example...
You will see that I copy pasted the code, exactly as you sent it...

THANKS A LOT YUDLUGAR!!!! I am going to try your now!

Yudlugar:

Runtime Error '1004':
Method 'Range' of object '_Global' failed


The Debug says the error is in line:
Range("D" & i) = Mid(Range("C" & i), 1, 3)

However, I guess that as in SAS, the error is at the end of the line above:

For count = Selection.Row To Selection.Row + Selection.Rows.count

I am just guessing... arent you just using count to define count?
Is it possible in VBA?

Thank you!

You guys rock! :*

@yudlugar - It should work for the number of rows in the selection

@desree86 - the entire affected range of columns (ie C-H) need to be highlighted/selected as well as the rows

Ooooh John, let me try that one!
Will let you know in a second

@John,

Have you tried it?

Select Rows 11 to 16 and try running your code... your comment regarding the C-H is incorrect, it doesn't make any difference.

with C11:H16 to selected,
y = 11 and x = 6
so your loop will go from 6 to 11 with -1 steps... which will just exit the loop instantly and do nothing.

@Desree86, I do not use i in my code... so not sure why that is an error on that line. Have you tried this code:

Please Login or Register  to view this content.

I've changed my variable to i to make mine and John's code more interchangeable.

Originally Posted by Desree86

Yudlugar:

Runtime Error '1004':
Method 'Range' of object '_Global' failed


The Debug says the error is in line:
Range("D" & i) = Mid(Range("C" & i), 1, 3)

However, I guess that as in SAS, the error is at the end of the line above:

For count = Selection.Row To Selection.Row + Selection.Rows.count

I am just guessing... arent you just using count to define count?
Is it possible in VBA?

Thank you!

You guys rock! :*

Desree86 can you show us your code? Yudlugar doesn't have an i in his loop?

Mmmmm, I might be doing something worng, but this does not do anything at all...
I tried selecting (C4: H6)
I tried selecting (D4 : H6)
Nothing happens...
Could it be because the string are in "text" format?
This is the string (in C4 --> C6) that I want to split into D4 E4 F4 G4 H4, D5 E5 F5 G5 H5, D6 E6 F6 G6 H6

0010311701
0020311629
0030311530

Thank you so much and sorry for being such a challenge!

I'm getting a bit lost with who is replying to who and things - it might be easier for you to deal with one person so I'll leave John to resolve your problem. I'm sure he will be able to sort this out for you.

Originally Posted by yudlugar

@John,

Have you tried it?

Select Rows 11 to 16 and try running your code... your comment regarding the C-H is incorrect, it doesn't make any difference.

with C11:H16 to selected,
y = 11 and x = 6
so your loop will go from 6 to 11 with -1 steps... which will just exit the loop instantly and do nothing.

@Desree86, I do not use i in my code... so not sure why that is an error on that line. Have you tried this code:

Please Login or Register  to view this content.

I've changed my variable to i to make mine and John's code more interchangeable.

I tested it. You have it inverted y = selection.row (the first row in the selection) - 6 , x = selection.rows.count (the last row in the selection) - 11. It loops from the bottom of the selection to the top.

Yudlugar,
My name is actually Alba Sorry that I did not say it from the beginning
Then, the code that was giving that error is the one that you super kindly provided me plus the " and so on " added and the name changed to "split"

That is:

Sub split()
Dim count

For count = Selection.Row To Selection.Row + Selection.Rows.count
Range("D" & i) = Mid(Range("C" & i), 1, 3)
Range("E" & i) = Mid(Range("C" & i), 4, 2)
Range("F" & i) = Mid(Range("C" & i), 6, 1)
Range("G" & i) = Mid(Range("C" & i), 7, 2)
Range("H" & i) = Mid(Range("C" & i), 9, 2)
Next

End Sub

@ yudlugar Ok, Yudlugar, thank you for your help...!

@ John, im reading and trying now and I will write in a few seconds

SO MANY THANKS TO BOTH!

@ John...

I used Yud's code. IT WORKS!!!!! You guys were totally right, I copy pasted the body with the "i" where there should be a "count".

The code is the following:


Sub split()
Dim count

For count = Selection.Row To Selection.Row + Selection.Rows.count
Range("D" & count) = Mid(Range("C" & count), 1, 3)
Range("E" & count) = Mid(Range("C" & count), 4, 2)
Range("F" & count) = Mid(Range("C" & count), 6, 1)
Range("G" & count) = Mid(Range("C" & count), 7, 2)
Range("H" & count) = Mid(Range("C" & count), 9, 2)
Next

End Sub

The only very last issue that I ahve now is that I selec from C4 to C6 and it rund the Macro as if I was selected from C4 to C7...
There must be a +1 some where that should be changed.

You guys really rock! GRACIAS!

Well, i fixed it by adding " -1" here:

For count = Selection.Row To Selection.Row + Selection.Rows.count - 1
I guessed right.
Sorry to have been such a night mare.
But now is GREAT!!!!
YOU ROCK!
THANKS A LOT!
HUGS!

Desree86:

Here is my input. You need to select the rows you want and the columns affected and run the code.

Please Login or Register  to view this content.

In post 18, you have it mixed up. Also use code tags when posting.

@Yudlugar: Sorry, I didn't mean to butt in. I was only offering a different approach, didn't think it would cause this much confusion.