Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA - Text To Columns
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA - Text To Columns
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 *** |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA - Text To Columns
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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combining Text from 2 Columns into 1 then Deleting the 2 Columns | Excel Worksheet Functions | |||
help with sorting text in columns to match other columns | Excel Discussion (Misc queries) | |||
merge text from 2 columns into 1 then delete the old 2 columns | Excel Worksheet Functions | |||
Linking text columns with text and data columns | Excel Worksheet Functions | |||
extracting text from within a cell - 'text to rows@ equivalent of 'text to columns' | Excel Programming |