Parsing number\word groups using REGEX

[tgvxz]

I have been trying to separate data which has been grouped into one field. The data has the format:

ID    DATA

01    12345678Help separate this (there can be several spaces) 23456789He/She can not fix it

02     23456789He/She can not fix it        

03     98741236This error cannot be fixed  (there can be several spaces)  12345678Help separate this (there can be several spaces) 23456789He/She can not fix it

The common separator is that each group to separate starts with an 8-digit number (/d{8}). The first group should start with the 8-digit number and end with the last word before the next group of 8-digit numbers.

I want the data to be separated out like this:

ID	DATA1	DATA2	DATA3
01	12345678Help separate this	23456789He/She can not fix it
02	23456789He/She can not fix it
03	98741236This error cannot be fixed	12345678Help separate this	23456789He/She can not fix it

[Yoshiro_Fujimori · Accepted solution]

@tgvxz ,

If each field starts with 8 digit numbers preceded by at least one space,

I would replace the one space with a separator (which is not used elsewhere. I used "|".)

Then parse the data with the separator with "Text To Columns" tool.

Does it work for you?

[tgvxz]

@Yoshiro_Fujimori 

Yes, this method did work. Thank you very much.

[jpowellsd]

Just saw this popup, and being a regex geek, thought I'd see if I could solve it with regex :)

[flying008]

Hi, @tgvxz 

FYI.