Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I am currently using a series of cells to break down a wall of text that is received from a machine on our manufacturing floor. It gives us a block of data because it doesn't know how to separate it (the machine is from the early 90s). This is an example line of what we get from the machine:

    __DATE__ __TIME__ ___M.O___ ___U.P.C.___ M A SER. TIME _BK__ B _CR__ C THICK T PRS P TK T _OOB_ O ANG A TOTAL T
    01/02/08 15.01.35 1026610 66253101058 2 . 0001 53.4 16.23 . 01.90 . 3.205 . 433 . 00 . 0 n 0 n 19.75 .

    I have various text functions in cells to break apart the first few portions, but it is a clumsy way of doing it because certain fields can have longer codes (such as M.O) so using formulas like Left(), Mid(), etc. can cause issues. What I'm looking to do is create a macro to break the line down into individual cells so that the non computer savvy folks who would like to see some of the data can easily view it without calling me every 5 minutes to type in a formula for them.

    Currently, this is what I'm doing to get the data from the columns:

    [A] Block of text
    [B] Date: =VALUE(LEFT(A2, 8))
    [C] Time: =MID(A2, 10, 8)
    [D] MO: =MID(A2, 19, 9)
    [E] UPC: =MID(A2, 28, 12)
    [F] Station: =MID(A2, 40, 1)
    [G] Hour: =LEFT(C2, 2)
    [H] Minute: =MID(C2, 4, 2)
    [I] Second: =RIGHT(C2, 2)
    [J] Seconds: =H2*60+I2
    [K] Cycle: =J2-J1

    I'm thinking that there must be a pretty easy loop I could use that went through the entire spreadsheet. Each field is separated by a space or a " . " which is a space, then a period, then a space. The psuedo code seems easy enough in my head, but I don't know the syntax at all

    EDIT: the " . " is actually a field with no data behind it. I'm counting 25 columns of data and the .'s would have to be included to reach that number of columns.

    Can anyone start me off in the right direction?
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts
    [quote name='officespacer' post='778165' date='03-Jun-2009 06:34']This is an example line of what we get from the machine:
    __DATE__ __TIME__ ___M.O___ ___U.P.C.___ M A SER. TIME _BK__ B _CR__ C THICK T PRS P TK T _OOB_ O ANG A TOTAL T
    01/02/08 15.01.35 1026610 66253101058 2 . 0001 53.4 16.23 . 01.90 . 3.205 . 433 . 00 . 0 n 0 n 19.75 .
    EDIT: the " . " is actually a field with no data behind it. I'm counting 25 columns of data and the .'s would have to be included to reach that number of columns.[/quote]
    Can you post a spreadhseet example - it's unclear if the data example is two lines, with the first line being the header, or what. Also, do you need ot preserve the " . " (space period space) an an empty data field, or can it be removed?

    (In the past, when there has been a significant volume of data, I have used a macro that runs a find and replace to set an unusual character such § as a separator, and then uses a text-to-columns transformation.)
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts
    here's 25 rows of data from my spreadsheet. I'm currently working on trying to write a for next inside another for next loop to do what i'm looking to do but I haven't gotten too far quite yet
    Attached Files Attached Files
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Select column A.
    Select Data | Text to Columns.
    Select Delimited, then click Next.
    Tick the check box for Space, then click Finish.

  5. #5
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts
    how anti climatic. so much for the 30 lines of non-working code I wrote

    Thanks Hans.
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts
    [quote name='officespacer' post='778183' date='03-Jun-2009 07:59'] how anti climatic. so much for the 30 lines of non-working code I wrote [/quote]
    You could still use a macro and look like an Excel expert:
    Code:
    Sub Macro1()
      With Selection.EntireColumn
    	.Replace What:="_", Replacement:=" "
    	.Replace What:="   ", Replacement:=" " ' What contains "three spaces"
    	.Replace What:="  ", Replacement:=" " ' What contains "two spaces"
    	.Cells(1).Value = Trim(.Cells(1).Value)
    	.TextToColumns Destination:=.Cells(1), DataType:=xlDelimited, _
    		ConsecutiveDelimiter:=True, Space:=True
    	.CurrentRegion.EntireColumn.AutoFit
      End With
    End Sub
    -John ... I float in liquid gardens
    UTC -7ąDS

  7. #7
    5 Star Lounger
    Join Date
    Jul 2004
    Location
    Ohio
    Posts
    629
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='JohnBF' post='778192' date='03-Jun-2009 16:46']You could still use a macro and look like an Excel expert:
    Code:
    Sub Macro1()
      With Selection.EntireColumn
    	.Replace What:="_", Replacement:=" "
    	.Replace What:="   ", Replacement:=" " ' What contains "three spaces"
    	.Replace What:="  ", Replacement:=" " ' What contains "two spaces"
    	.Cells(1).Value = Trim(.Cells(1).Value)
    	.TextToColumns Destination:=.Cells(1), DataType:=xlDelimited, _
    		ConsecutiveDelimiter:=True, Space:=True
    	.CurrentRegion.EntireColumn.AutoFit
      End With
    End Sub
    [/quote]

    Thanks John, now they'll think I'm a pro!
    <img src=/w3timages/blueline.gif width=33% height=2>
    <big>John</big>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •