0

So here is what I am looking to do. I have a spreadsheet with a cell that has a string of numbers in it (some are longer than others) I need to extract every eight numbers and put them into single rows.

Here is what the current spreadsheet looks like:

IFIELD1     IFIELD2   IFIELD3     IFIELD4    IMAGELIST
05/30/2009  ERMS      100 Day     L. C.      0000001200000013
05/30/2009  ESE       20 Day      J. R.      0000001400000015
05/30/2009  IHS       100 Day     H. W.      00000016
05/30/2009  LMS       100 Day     D. E.      0000001700000018

This is what I am looking to get:

IFIELD1     IFIELD2   IFIELD3   IFIELD4   IMAGELIST
05/30/2009  ERMS    100 Day     L. C.     00000012
05/30/2009  ERMS    100 Day     L. C.     00000013
05/30/2009  ESE     20 Day      J. R.     00000014
05/30/2009  ESE     20 Day      J. R.     00000015
05/30/2009  IHS     100 Day     H. W.     00000016
05/30/2009  LMS     100 Day     D. E.     00000017
05/30/2009  LMS     100 Day     D. E.     00000018

Some sets off numbers go up to 30 or more groups of 8 in a line. With over 1000 rows of data to separate. Is there any type of code that will help make this process less daunting? I am currently using Left, mid and right but this is going to take me years to finish. The worksheets are coming out of access so if there is an easier way to complete it in there I am open to suggestions. The final product must be saved in a csv. Also I need to keep the entire 8 digits in the completed product prior to saving as a csv so that I can create the link to where that image is located. Thank you for your help!

Melissa
  • 9

3 Answers3

0

There's a fairly easy solution in Access.

  1. Create a table that is nothing but a list of numbers: 1, 2, 3, 4, ... n where n is the most number of items that would ever be in your IMAGELIST. It's not a very high cost to make it larger than needed, though, so go up to 50 or something.
  2. Create a query that pulls in your original table and the numbers table without any joins. If you just did SELECT * at this point, you'd get n copies of every record in your original table.
  3. Instead of pulling in the IMAGELIST field, add a formula that performs the MID function on the IMAGELIST field based on the value from your numbers table.
  4. Filter to only show records where the formula doesn't return a blank string. This will mean that a record with IMAGELIST 24 characters long only shows up 3 times instead of n times with a bunch of blank records.

Here's the SQL for that query:

SELECT Table1.ID, Table1.IFIELD1, Table1.IFIELD2, Table1.IFIELD3, Table1.IFIELD4, Table2.Number, Mid([IMAGELIST],([Number]-1)*8+1,8) AS ImageListParse
FROM Table1, Table2
WHERE Mid([IMAGELIST],([Number]-1)*8+1,8)<>""
ORDER BY Table1.ID, Table2.Number;

In my quick mockup, Table1 is your original table, Table2 is the numbers table, Number is the only field in Table2 and it's just the numbers 1-n, and I added the autonumber field ID to Table1 just for sorting.

This all assumes that every item in IMAGELIST is exactly 8 characters. If it varies in length, this approach won't work.

Engineer Toast
  • 5,218
0

This is not terribly hard to do with Excel worksheet functions.  Let’s assume that your data are on Sheet1 with headers in Row 1.  Create Sheet2.  Fill it in as follows:

  • A1 → =Sheet1!A1
    • and drag to the right to cover all the columns (i.e., through Column E, based on your example of five columns),
    • or simply copy the headers from Sheet1 to Sheet2.
  • A2 → =OFFSET(Sheet1!A$1, $F2-1, 0)
    • and drag to the right to cover all the columns except for IMAGELIST (i.e., through Column D).
    • At some point you’ll want to format the Sheet2 columns correctly (i.e., the ones that contain dates).
  • E2 → =LEFT($G2, 8)
  • F2 → 2
  • G2 → =OFFSET(Sheet1!E$1, $F2-1, 0)
    • (This is the same as the formulas in Columns A-D, except referencing Sheet1, Column E.)
  • H2 → =RIGHT($G2, LEN($G2)-8)

Drag/fill Row 2 to Row 3.  Then change

  • F3 to =IF(H2<>"", F2, F2+1), and
  • G3 to =IF(H2<>"", H2, OFFSET(Sheet1!E$1, $F3-1, 0))

Now drag/fill Row 3 down for as many rows as you need to get all the data from Sheet1:

Then copy and paste values & formats.

Explanation:

  • Column F indicates which row on Sheet1 this row (on Sheet2) is pulling data from.
  • Each time we advance to a new Sheet1 row (i.e., each time Column F increases), Column G gets a complete, 8×n-digit-long IMAGELIST value from Sheet1.
  • The value in Column G gets split: first eight digits into Column E and the remainder into Column H.
  • As long as we have a non-empty remainder in Column H, we copy it into Column G and leave Column F the same (i.e., accessing the same row on Sheet1).
  • When we run out of data in Column H, that means we’ve decomposed an 8×n-digit-long IMAGELIST value from Sheet1 into n rows on Sheet2, so we increment Column F and fetch the next IMAGELIST value.
Scott - Слава Україні
  • 22,532
0

There are two steps you need to do to achieve your goal:

1 split the numbers into different columns,e.g. In Excel 2010: Data - text to columns - fixed width - set width to 8 then click finish.

2 2nd step is to unpivot your data, unfortunately it's not simple, but there're are a lot of solutions in the internet, like this: Is it possible to "unpivot" or "reverse pivot" in Excel?

Community
  • 1
Máté Juhász
  • 22,071