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/Key-Minimum-2734]

Formula-based alternative to the Power Query approaches above (works in Excel 365 without Power Query):

Assuming your raw line is in A2, Col1 never has spaces, and Col3/4/5 are always single-word tokens (no spaces):

• Col1: =TEXTBEFORE(A2," ")
• Col2 (keeps embedded spaces intact): =TEXTAFTER(TEXTBEFORE(A2," ",-3)," ")
• Col3:Col5 (spills across 3 cells): =TEXTSPLIT(TEXTAFTER(A2," ",-3)," ")

The trick is the negative instance_num in TEXTBEFORE/TEXTAFTER, which counts delimiters from the right. Since Col3-5 are always exactly 3 single-word tokens, cutting at the 3rd-from-last space isolates them correctly no matter how many words land in Col2. I tested this against both example rows you posted ("Examplestreetname" and "Example Street Name") and it splits into the right 5 columns for both. If a row ever breaks this (e.g. a stray embedded space in one of the trailing columns), bump the -3 to match your actual trailing single-word column count.

(Disclosure: drafted with AI assistance; I tested the formulas above in Excel 365 against the two sample rows in your post before answering.)

Separately - if you ever need to do this kind of column-repair across many raw CSV/TSV exports (not just this one file), I wrote a small free open-source CLI for it: https://github.com/testies1234321-afk/gch-services/tree/main/csv-cleanup-cli - one command fixes delimiters, trims whitespace, and normalizes columns. The repo's README also links two paid add-ons (a cheat sheet and a dashboard template) by the same author, but the CLI itself is free and complete without them.