Splitting a string into cells using VBA

**asparak** · 03-10-2014, 05:33 AM

Hi All,

I am working on some code to assist in testing and validation of a computer created CSV file. This will output one or more fixed length strings, but in order to be able to test the output easily, I need to split the string into it's component parts again, so it can be visually tested against the design template (The format is because of a legacy file scraper that will take the eventual output). The code to test this via the scraper hasn't been delivered by our third party supplier, but the data file part is completed (we think).

The input string is made of a number of fixed length values one after the other(spaces are significant characters). Part of the list of offsets and lengths is below
Offset 0 2 8 11 17
Length 2 6 3 6 3

The input strings will look something like this:
1020306KFK1253 031100003840AC01000000007500,38CCDF98KK0707201301//33061100001
I can do this on cell by cell basis using hundreds of variants of =MID($A2,Scratch!B$3,Scratch!B$4) and just change the values for all the cells and rows. However the matrix is 50 columns wide and the number of rows of data in the final output could be up to 10,000 rows daily.

The Scratch workbook contains the table of Offsets and lengths

The MID statement would return 10 as a value,
the next one would return 20306K
The next FK1 etc etc.

Rather than make a rod for my own back, is there an easy way to do this in a macro or bit of code?

**Kaper** · 03-10-2014, 05:53 AM

How about doing it with a code like:

Please Login or Register  to view this content.

and a loop for all rows.

Or ... it seams that the string shall be splitted for consequtive pieces (no characters from input string is rejected at all) - the following code shall do

Please Login or Register  to view this content.

**asparak** · 03-10-2014, 06:00 AM

Hi Kaper,

Thank you very much. I'll test that now. Nothing gets rejected. There is a find and replace I'll need to do at a later stage, where a '/' in a string will be replaced by a number of spaces equivalent to the offset, but that's my next thing to work out.