Cut parts of data in cell with var delimiters to separate cells and copying adjacent data

[Noah14]

Hi Excel Experts,

a somewhat inconsistent data download leaves me with a messy file that needs to be re-structured (see uploaded sample file, sheet download).

The problem is that a cell may contain separate data entries (five alphanumerical characters), which are separated by different delimiters (that's why text import and conversion won't work). Delimiters may be, for example, a single or a number of consecutive spaces, line break, colon etc.

I'd like to have this data in the cell split to separate rows and the order of the original columns swaped. To the left and right of the messy column, there is also cells with data, that should be added per row in the columns next to the one with the split values (desired output also shown in uploaded sample file, sheet restructured). Since I have several source file with like 2000 rows, doing it manually would take weeks and would be very prone to typos.

Thanks for your help,
Regards,
Noah

[GeneralDisarray]

SEE ATTACHED FILE:

You will need to give the macro the proper Source and Target worksheet names (highlighted below)

Please Login or Register  to view this content.

[Noah14]

HI GeneralDisarray,

sorry for the late response. Your code works great already. But I noticed there actually is also some strings that are five characters in length, so either five or six (alphanumerical both). Is there a way to somehow add it in?
Regards,
Noah

[GeneralDisarray]

Yes, but it become more difficult.

Are the alpha-numeric string only 5 or 6 characters in length?

[Noah14]

Yes, only 5 or 6. Actually starting with a number and ending with a letter

[GeneralDisarray]

Sorry - meant to answer but got swamped with other things.


Best way forward would be to just code in a way to find the 'dataLength' as part of the problem - previously I set this as a constant ah the top of the module. Now, instead of a constant we can use a DIM statement to make that value change as needed..

The new For loop will find the proper length - it could be any length, not just the 5 or 6.

Please Login or Register  to view this content.

[Noah14]

Thank you so much GeneralDisarray. Works perfect and saves weeks of manual work!

[GeneralDisarray]

You are welcome, thank you for the feedback