Correct approach? (Multiple MID functions)

Hello - I just joined this forum and I am very happy to have found this place! Here is my question:

* I have a complex Excel workbook with several worksheets.
* The first worksheet is for raw data, which consists of a single string of data, separated by spaces, to be uploaded to a mainframe. There is no consistent pattern of length of data or strings in between pieces of data, which can vary widely. An example of the data would be something like this:

XXXXX YYYYY NN XXXXXXX

* For testing purposes, I need to come up with a way to parse the data for easier viewing. The current approach is this:

* Take the string and divide it up into individual cells by using the MID function. In the above approach, this would split the data into four (4) different cells.

The main disadvantage that I see are how to maintain all these MID functions without going crazy, LOL.

This is going to be a very complex workbook with data strings that can have 20+ elements in them, and have lots of separate records, etc. While this way should work (if everything is entered perfectly and no data structures ever change, etc.), I was wondering if conceptually, there is an easier way to accomplish my goal.

Thanks in advance for your time and consideration!

Thanks for the quick reply! I had wanted to use this command, but the spaces between the data are not uniform. Also, it is a space character and not a comma or tab.

For example, the data string (all on the same line) may look something like this:

XXXXXXXXX (then add 6 spaces)
YYYYYY (then add 3 spaces)
NNNNNNNN (then add 11 spaces)
and so on....

Any more ideas how this could work?

Yes, but will this work if the data strings (within each record) are different lengths each time?

In other words, the column lengths may differ, but will always be constrained by a maximum length. Then, somehow I would need to trim off the extra spaces, but this number of spaces may be different from record to record.

Does that make sense?

Hi Max,

It is difficult to post a data sample, because the extra spaces keep getting stripped out. Imagine something like this (where the phrase "extra spaces" is literally tapping the spacebar), all in a single string:

XXXXXX (plus six spaces)
NNNNN (plus eleven spaces)
XXX (plus three spaces)
and so forth

What I want the result to be is to parse out the string onto a separate worksheet, with each separate data value represented in the string to be placed in a separate column. (The purpose of this is so the individual columns can be filtered to inspect the data more easily, rather than inspecting a single long string.)

Does this make sense?

Hi Harmony,

I've attached what I believe to be an example of the data you receive (Sheet 1) and the format you want it in (Sheet 2). Can you please confirm this is the case?

Dave

Hi sweep,

Yes, that is very similar!

Thanks so much! This seems to be working and a *lot* simpler than using compounded MID functions