VBA - Text To Columns

[ajocius]

Group,
How do I convert 140 pieces of data that are comma delimited using VBA? The problem is Excel only converts text into 125 columns of data. How do I ensure the remainder of the data is placed on the spreadsheet. The data looks something like:

Seek Button, 4, Power Button 8, Hi Band Level, 5.04, Low Band Level 2.32...
Seek Button, 4, Power Button 8, Hi Band Level, 5.36, Low Band Level 3.24...
Seek Button, 4, Power Button 8, Hi Band Level, 5.01, Low Band Level 2.76...
Seek Button, 4, Power Button 8, Hi Band Level, 5.45, Low Band Level 2.36...
Seek Button, 4, Power Button 8, Hi Band Level, 5.64, Low Band Level 2.47...
Seek Button, 4, Power Button 8, Hi Band Level, 5.94, Low Band Level 2.35...

Ultimately I'd like to have something like this...
Seek Button Power Button Hi Band Level Low Band Level
4 8 5.04 2.32
4 8 5.36 3.24 etc.

Tony

[Edward Ulle]

You don't say how you get the data, and I wasn't sure if you missed some
commas in your data sample but I have a macro that will split a comma
delimited string. I added a comma after Power Button and Low Band Level
to match the columns you described. Granted a lot of assumption on my
part but it may get you started.

Option Explicit

Sub Test()

Dim strText As String
Dim strArray As Variant
Dim i As Long
Dim iCol As Long
Dim iRow As Long

strText = "Seek Button, 4, Power Button, 8, Hi Band Level, 5.04, Low
Band Level, 2.32"
strArray = Split(strText, ",")

iCol = 0
iRow = 0
For i = LBound(strArray) To UBound(strArray) Step 2
Range("A1").Offset(iRow, iCol) = strArray(i)
Range("A1").Offset(iRow + 1, iCol) = strArray(i + 1)
iCol = iCol + 1
Next
iRow = iRow + 1

' For next row only store second value

End Sub



*** Sent via Developersdex http://www.developersdex.com ***

[James D. Connelly]

Would it not be easier to put this file into Notepad, copy one row and
remove all the number values...so it would look like Seek Button, Power
Button, Hi Band Level, Low Band Level...
Then do a CTRL <H> to activate search and replace. Type in Seek Button, as
the search parameter and leave the replace blank. That would remove all the
Seek Button Instances (if you turn of the prompt for replace it will do it
automatically except the problem is it will remove the Row you just created
so ensure you have saved that row as a separate notepad CSV file. When you
have done this for each Header option (Text entry) you will be left with a
comma delimted file of just number values..import these into a spreadsheet
and then put in your header ROW of Text Values.

I was a little confused about whether you have Seek Button 5, or Seek
Button, 5, as the values...if you have Seek Button 5, not Seek Button, 5,
then you would use Seek Button (note that you want the space after Button )
as the search parameter and leave the replace parameter as blank. Again
this will leave 5, as the remaining value. Doing this for each Text value
will leave just the numbers and you will quickly have a CSV file of just the
values. Again after you import or open it in excel you would add the header
row by typing it in. Much quicker than creating a VBA solution unless you
do this as a daily function or something.

Sometimes it is quicker and easier to use other options than VBA LOL. You
could write a VBA routine that does all this, but like I say unless this is
something you will be doing a lot of times why bother? If it is something
you will be doing a lot of, let me know and we can try writing something.

James D. Connelly
----------------------------------------
116 Rice Ave
Hamilton, ON L9C 5V9
Phone (905) 575 0284
----------------------------------------
Primary Web Site - http://home.moutaincable.net/~csctraders/cardznutz
Secondary Mirror Site = http://www.freewebs.com/cardznutz
Proud Member of CSCT (Canadian Sports Card Traders) Group


"ajocius" <[email protected]> wrote in
message news:[email protected]...
>
> Group,
> How do I convert 140 pieces of data that are comma delimited using
> VBA? The problem is Excel only converts text into 125 columns of data.
> How do I ensure the remainder of the data is placed on the spreadsheet.
> The data looks something like:
>
> Seek Button, 4, Power Button 8, Hi Band Level, 5.04, Low Band Level
> 2.32...
> Seek Button, 4, Power Button 8, Hi Band Level, 5.36, Low Band Level
> 3.24...
> Seek Button, 4, Power Button 8, Hi Band Level, 5.01, Low Band Level
> 2.76...
> Seek Button, 4, Power Button 8, Hi Band Level, 5.45, Low Band Level
> 2.36...
> Seek Button, 4, Power Button 8, Hi Band Level, 5.64, Low Band Level
> 2.47...
> Seek Button, 4, Power Button 8, Hi Band Level, 5.94, Low Band Level
> 2.35...
>
> Ultimately I'd like to have something like this...
> Seek Button Power Button Hi Band Level Low Band Level
> 4 8 5.04
> 2.32
> 4 8 5.36 3.24
> etc.
>
> Tony
>
>
> --
> ajocius
> ------------------------------------------------------------------------
> ajocius's Profile:
http://www.excelforum.com/member.php...o&userid=17695
> View this thread: http://www.excelforum.com/showthread...hreadid=498134
>