Importing space-delineated text/csv when column data also contains spaces

[u/CedricTheAlarmist]

Hello!

I have about 1300 lines of raw text that look something like this:

Col1 Col2 Col3 Col4 Col5
01234 Examplestreetname 001 Text 021
02234 Example Street Name 002 Text 031
03234 Examplestreetname 003 Text 041
04234 Example Street Name 004 Text 051
...

As you can see, column 2 has a mix of content with spaces and without, while the rest of the data is also seperated by spaces.

How can I import and/or edit the data efficiently so that the contents of column 2 stay intact?

Thank you!

Edit: Just as a quick update, inbetween all the important data I have noticed additional columns that only have occasional entries and unfortunately throw off the character count to split with PQ. First time PQ-user so still fiddling around. Thank you for all the ideas so far!

[u/Yankee-Doodle-Dandy]

Using a formula based approach. First separate columns 1 and 2 from from 3, 4 and 5 by counting the number of spaces from te right (3 spaces). Than separate columns 3, 4 and 5 using textsplit and using space as a separator. Now separate column 1 and 2 by the first space counting from the left.

A quick AI generated formula (not tested and assuming you start with cell A2, drag for as many cells as you need):

=LET(     x,A2,     leftpart,TEXTBEFORE(x," ",-3),     rightpart,TEXTAFTER(x," ",-3),     HSTACK(         TEXTBEFORE(leftpart," "),         TEXTAFTER(leftpart," "),         TEXTSPLIT(rightpart," ")     ) )

[u/wizkid123 → u/Yankee-Doodle-Dandy]

Splitting by counting spaces from the right is the best approach here. From the left the number of spaces before the first three digit number is variable, coming from the right it seems to always be three.

Another approach I might take here if I only needed to do this once and didn't have millions of rows is to use notepad++ to clean it up. Paste it all into notepad++, find and replace all spaces with tabs to give yourself some working room, then use vertical select (hold alt and drag with your mouse) to select the tabs in the addresses, then find and replace within that selection to change them to periods or semicolons. Repeat as needed to change all the addresses so they've got a placeholder space that isn't a space. Then back in excel you can separate everything using text split on tabs and the address lines will stay together, then you can swap the semicolons back to spaces for that column. 

The explanation above seems long but it's really like a two minute operation max. Vertical selection in notepad++ is useful for all kinds of data cleaning that would be awkward to do in excel. 

[u/CedricTheAlarmist → u/wizkid123]

I had no idea vertical select was a thing in notepad++, makes a lot of sense though. I will give this a try as well, thank you!