Tags: build, columns, convert, excel, ignorant, macro, microsoft, msdn, multiple, offixed, rows, sequentially, software, text, vba, width

VBA text to columns

On Microsoft » Microsoft Excel

14,575 words with 9 Comments; publish: Sun, 01 Jun 2008 12:38:00 GMT; (30697.66, « »)

Please help. VBA Ignorant. I am trying to build a macro to convert a lot of

fixed width data from text to columns sequentially for multiple rows. The

macro recorder will not work due its nature with arrays. The simple keyboard

commands that do work are as follows:

Alt D

Alt e

Enter

Enter

Enter

Arrow down

Got the books on VBA but can't seem to get started on this simple repetitive

onerous manual task. A keyboard recorder would be nice.

Thanks for your kind assistance

Parusky

All Comments

Leave a comment...

  • 9 Comments
    • Can you post the text file at this website. Open the text file with

      notebook. sometimes it is easier to make the conversions on the text file

      instead doing it on the worksheet.

      I like converting the text files to CVS files and then reading them into

      excel.

      "Parusky" wrote:

      > Please help. VBA Ignorant. I am trying to build a macro to convert a lot of

      > fixed width data from text to columns sequentially for multiple rows. The

      > macro recorder will not work due its nature with arrays. The simple keyboard

      > commands that do work are as follows:

      > Alt D

      > Alt e

      > Enter

      > Enter

      > Enter

      > Arrow down

      > Got the books on VBA but can't seem to get started on this simple repetitive

      > onerous manual task. A keyboard recorder would be nice.

      > Thanks for your kind assistance

      > Parusky

      >

      >

      >

      #1; Sun, 01 Jun 2008 12:39:00 GMT
    • Why won't the macro recorder work?

      It usually works ok for me when I record data|text to columns.

      The only time I've seen any problem with an array is when I have lots and lots

      of fields and get an "out of memory" error when I try to rerun the recorded

      macro.

      http://support.microsoft.com/default.aspx?scid=KB;EN-US;q134826&

      XL: "Out of Memory" Message Using the OpenText Method

      Parusky wrote:

      > Please help. VBA Ignorant. I am trying to build a macro to convert a lot of

      > fixed width data from text to columns sequentially for multiple rows. The

      > macro recorder will not work due its nature with arrays. The simple keyboard

      > commands that do work are as follows:

      > Alt D

      > Alt e

      > Enter

      > Enter

      > Enter

      > Arrow down

      > Got the books on VBA but can't seem to get started on this simple repetitive

      > onerous manual task. A keyboard recorder would be nice.

      > Thanks for your kind assistance

      > Parusky

      Dave Peterson

      #2; Sun, 01 Jun 2008 12:40:00 GMT
    • Dear Joel,

      The data is a copy and paste from a PDF so its not worth converting it to

      another form. This data is text in a single cell column for each row and

      each text item contains 12 groups of data. The data looks like this after a

      paste(Which wraps in this mail format for some of the bigger numbers):

      7770 6255 7340 7250 7725 8578 8511 9837 11185 9250 9775 10965

      870 855 735 1050 455 455 420 775 60 0 35 420

      116307 96218 105543 103700 105331 93772 112356 124627 115915 101216 122717

      118983

      74703 61472 68702 61263 64609 57197 67012 78771 74977 62717 80202 76894

      27541 23856 19972 22863 27967 25465 31096 37185 28061 26462 28705 25847

      The keystrokes I wrote down work fine for parsing it into columns since it

      is all fixed width delimited.

      Whenever a macro is recorded however it records a different array for each

      row. If you try relative positioning it applys the array to the next cell

      down. If that array is different then the results are in error.

      The macro recorder will not work unless the data groups are all the exact

      same length.

      Parusky

      "Joel" wrote:

      [vbcol=seagreen]

      > Can you post the text file at this website. Open the text file with

      > notebook. sometimes it is easier to make the conversions on the text file

      > instead doing it on the worksheet.

      > I like converting the text files to CVS files and then reading them into

      > excel.

      > "Parusky" wrote:

      #3; Sun, 01 Jun 2008 12:41:00 GMT
    • Dear Dave,

      It works in Lotus but not here. I suspect that the macro recorder records

      the results of the R2C wizard's evaluation of the data rather than the

      keystrokes required to progress through the wizard. The recorded VBA

      programs look like specific arrays that are data specific. When the recorded

      macro is run against another set of different data the results are not

      accurate. The recorder just does not record the keystrokes with Wizards.

      My posted keystroke sequence works fine. It just gets tiring after doing it

      for the 4 or 500th time in a row.

      BTW I can record a macro for each different text string length and run a

      macro for each row. This still means I have to key in something for each row.

      The idea is to write a VBA program in a relative loop that duplicates the

      keystrokes noted.

      Pa

      "Dave Peterson" wrote:

      > Why won't the macro recorder work?

      > It usually works ok for me when I record data|text to columns.

      > The only time I've seen any problem with an array is when I have lots and lots

      > of fields and get an "out of memory" error when I try to rerun the recorded

      > macro.

      > http://support.microsoft.com/default.aspx?scid=KB;EN-US;q134826&

      > XL: "Out of Memory" Message Using the OpenText Method

      >

      > Parusky wrote:

      > --

      > Dave Peterson

      >

      #4; Sun, 01 Jun 2008 12:42:00 GMT
    • I don't understand.

      If you're depending on excel to guess where the field breaks should be, then it

      sure sounds like the data could be parsed by using space as the delimiter.

      But maybe I'm missing something.

      Parusky wrote:[vbcol=seagreen]

      > Dear Dave,

      > It works in Lotus but not here. I suspect that the macro recorder records

      > the results of the R2C wizard's evaluation of the data rather than the

      > keystrokes required to progress through the wizard. The recorded VBA

      > programs look like specific arrays that are data specific. When the recorded

      > macro is run against another set of different data the results are not

      > accurate. The recorder just does not record the keystrokes with Wizards.

      > My posted keystroke sequence works fine. It just gets tiring after doing it

      > for the 4 or 500th time in a row.

      > BTW I can record a macro for each different text string length and run a

      > macro for each row. This still means I have to key in something for each row.

      > The idea is to write a VBA program in a relative loop that duplicates the

      > keystrokes noted.

      > Pa

      > "Dave Peterson" wrote:

      Dave Peterson

      #5; Sun, 01 Jun 2008 12:43:00 GMT
    • Dear Dave,

      The delimiter is sort of moot.

      The data string length of the text is different for each row.

      Never-the-less, the R2Column function parses it fine using fixed width. The

      problem is that the Macro recorder cannot record a function where it assigns

      a value to a function.

      For example, the following is an example of unparsed data with each segment

      equal to one cell:

      7770 62557340 72507725 85788511 983711185 9250 9775 1096

      870 855 735 1050 455 455 420 775 60 0 35 420

      116307 96218 105543 103700 105331 93772 112356 124627 115915 101216 122717

      118983

      The recorded macro to parse these three cells into 12 columns of data

      correctly which was generated from the original posting commands is as

      follows:

      Selection.TextToColumns Destination:=Range("D8"), DataType:=xlFixedWidth, _

      FieldInfo:=Array(Array(0, 1), Array(4, 1), Array(9, 1), Array(14,

      1), Array(19, 1), _

      Array(24, 1), Array(29, 1), Array(34, 1), Array(39, 1), Array(45,

      1), Array(50, 1), Array( _

      55, 1))

      Range("D9").Select

      Selection.TextToColumns Destination:=Range("D9"),

      DataType:=xlFixedWidth, _

      FieldInfo:=Array(Array(0, 1), Array(3, 1), Array(7, 1), Array(11,

      1), Array(16, 1), _

      Array(20, 1), Array(24, 1), Array(28, 1), Array(32, 1), Array(35,

      1), Array(37, 1), Array( _

      40, 1))

      Range("D10").Select

      Selection.TextToColumns Destination:=Range("D10"),

      DataType:=xlFixedWidth, _

      FieldInfo:=Array(Array(0, 1), Array(6, 1), Array(12, 1), Array(19,

      1), Array(26, 1), _

      Array(33, 1), Array(39, 1), Array(46, 1), Array(53, 1), Array(60,

      1), Array(67, 1), Array( _

      74, 1))

      End Sub

      Note that the cells are not relative when in reality it would be. Each line

      has a different array solution based on the data. If I were to run this

      macro on another set of data with a different length it does not and cannot

      work.

      All I wish to do is start on the first cell and duplicate the key strokes of :

      ALT D,e

      Enter

      Enter

      Enter

      For each cell

      Thanks for considering the problem.

      Cordially,

      PA

      "Dave Peterson" wrote:

      > I don't understand.

      > If you're depending on excel to guess where the field breaks should be, then it

      > sure sounds like the data could be parsed by using space as the delimiter.

      > But maybe I'm missing something.

      > Parusky wrote:

      > --

      > Dave Peterson

      >

      #6; Sun, 01 Jun 2008 12:44:00 GMT
    • First, Did you try Data|Text to columns using delimited by space (treating

      consecutive delimiters as one)?

      If no, then try it. You may be pleasantly surprised.

      And you wouldn't have to do each cell one by one, either.

      =========

      But...

      I put this in A1:

      7770-6255--7340-7250--7725-8578--8511-9837--

      11185-9250-9775-1096

      (Where each hyphen represents a space character--and there was no line wrap

      within A1)

      I manually ran data|text to columns|fixed width|just clicked next.

      Each number was put into its own cell:

      A1:L1 (Transposed for posting)

      7770

      6255

      7340

      7250

      7725

      8578

      8511

      9837

      11185

      9250

      9775

      1096

      Is each of these numbers a "segment". I'm not sure what that means.

      Do you get different results?

      ========

      If you want to fiddle with just mimicking keystrokes, you may want to look at

      sendkeys in VBA's help. I wouldn't use this. I wouldn't recommend others use

      it either.

      Parusky wrote:[vbcol=seagreen]

      > Dear Dave,

      > The delimiter is sort of moot.

      > The data string length of the text is different for each row.

      > Never-the-less, the R2Column function parses it fine using fixed width. The

      > problem is that the Macro recorder cannot record a function where it assigns

      > a value to a function.

      > For example, the following is an example of unparsed data with each segment

      > equal to one cell:

      > 7770 6255 7340 7250 7725 8578 8511 9837 11185 9250 9775 1096

      > 870 855 735 1050 455 455 420 775 60 0 35 420

      > 116307 96218 105543 103700 105331 93772 112356 124627 115915 101216 122717

      > 118983

      > The recorded macro to parse these three cells into 12 columns of data

      > correctly which was generated from the original posting commands is as

      > follows:

      > Selection.TextToColumns Destination:=Range("D8"), DataType:=xlFixedWidth, _

      > FieldInfo:=Array(Array(0, 1), Array(4, 1), Array(9, 1), Array(14,

      > 1), Array(19, 1), _

      > Array(24, 1), Array(29, 1), Array(34, 1), Array(39, 1), Array(45,

      > 1), Array(50, 1), Array( _

      > 55, 1))

      > Range("D9").Select

      > Selection.TextToColumns Destination:=Range("D9"),

      > DataType:=xlFixedWidth, _

      > FieldInfo:=Array(Array(0, 1), Array(3, 1), Array(7, 1), Array(11,

      > 1), Array(16, 1), _

      > Array(20, 1), Array(24, 1), Array(28, 1), Array(32, 1), Array(35,

      > 1), Array(37, 1), Array( _

      > 40, 1))

      > Range("D10").Select

      > Selection.TextToColumns Destination:=Range("D10"),

      > DataType:=xlFixedWidth, _

      > FieldInfo:=Array(Array(0, 1), Array(6, 1), Array(12, 1), Array(19,

      > 1), Array(26, 1), _

      > Array(33, 1), Array(39, 1), Array(46, 1), Array(53, 1), Array(60,

      > 1), Array(67, 1), Array( _

      > 74, 1))

      > End Sub

      > Note that the cells are not relative when in reality it would be. Each line

      > has a different array solution based on the data. If I were to run this

      > macro on another set of data with a different length it does not and cannot

      > work.

      > All I wish to do is start on the first cell and duplicate the key strokes of :

      > ALT D,e

      > Enter

      > Enter

      > Enter

      > For each cell

      > Thanks for considering the problem.

      > Cordially,

      > PA

      > "Dave Peterson" wrote:

      Dave Peterson

      #7; Sun, 01 Jun 2008 12:45:00 GMT
    • If Dave's advise has not worked, please explain why you think you have

      "fixed width data", when in other posts you appear to have variable length,

      space separated data.

      NickHK

      "Parusky" <Parusky.excel.todaysummary.com.discussions.microsoft.com> wrote in message

      news:F6443A3F-5A24-447F-8E18-9B7382CA019A.excel.todaysummary.com.microsoft.com...

      > Please help. VBA Ignorant. I am trying to build a macro to convert a lot

      of

      > fixed width data from text to columns sequentially for multiple rows. The

      > macro recorder will not work due its nature with arrays. The simple

      keyboard

      > commands that do work are as follows:

      > Alt D

      > Alt e

      > Enter

      > Enter

      > Enter

      > Arrow down

      > Got the books on VBA but can't seem to get started on this simple

      repetitive

      > onerous manual task. A keyboard recorder would be nice.

      > Thanks for your kind assistance

      > Parusky

      >

      >

      >

      #8; Sun, 01 Jun 2008 12:46:00 GMT
    • I ain't giving up on this either <bg>.

      NickHK wrote:[vbcol=seagreen]

      > If Dave's advise has not worked, please explain why you think you have

      > "fixed width data", when in other posts you appear to have variable length,

      > space separated data.

      > NickHK

      > "Parusky" <Parusky.excel.todaysummary.com.discussions.microsoft.com> wrote in message

      > news:F6443A3F-5A24-447F-8E18-9B7382CA019A.excel.todaysummary.com.microsoft.com...

      > of

      > keyboard

      > repetitive

      Dave Peterson

      #9; Sun, 01 Jun 2008 12:47:00 GMT