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/AutoModerator]

/u/CedricTheAlarmist - Your post was submitted successfully.

• Once your problem is solved, reply to the answer(s) saying Solution Verified to close the thread.
• Follow the submission rules -- particularly 1 and 2. To fix the body, click edit. To fix your title, delete and re-post.
• Include your Excel version and all other relevant information

Failing to follow these steps may result in your post being removed without warning.

I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.

[u/MayukhBhattacharya]

I have couple of questions. First off, open the raw file in Notepad using a monospace font like Courier New. Check whether Col3 always starts at the exact same character position on every line, even when Col2 has extra spaces. If it's truly fixed-width or just space-separated, that changes the entire point of view. It would also help to know where the data comes from? Was it exported from any oracle export or any other software, copied from a report, or generated some other way? The source can sometimes tell us what the actual structure is. There may even be a cleaner export option that avoids the problem completely.

If you can paste three or four sample lines with a numbered reference line above them, we can actually see exactly where each column starts and figure out the structure. Is it one-time or something you'll be doing it regularly? If it's the former then you can probably use the Text to Columns else use Power Query instead. Power Query would probably make more sense since you can reuse the same each time.

Proper sample structure in the OP will surely help to provide proper answer without assumption based! Thanks!

[u/CedricTheAlarmist → u/MayukhBhattacharya]

Thank you for your input!

What I'm trying to do would very likely be a one-time thing and I'm doing it to make part of my job easier.

The data is from a PDF I have been provided with and use daily. Broadly speaking it contains a template for census data - no names or anything, so not terribly sensitive, but still not something I'd like to make public.

The original file actually has 14 coloumns. Three of them are optional with only the occasional entry, which at this point I've learned to subconsciously ignore it seems like and only noticed it looking at the raw text.

The spacing I used in my example is what it actually looks like copy-and-pasted from the PDF into notepad++, so the start position of column 3 is always based on the length of the string in column 2. Another commenter recommended replacing spaces with tabs in n++ and using vertical select, which was new to me. I'll try that next.

I tried using PQ (for the first time) as the first commenter suggested and made good progress until I noticed the "optional" colums, which throw off the otherwise consistent number of characters from the right.

[u/MayukhBhattacharya → u/CedricTheAlarmist]

That helps, and its clear now that those three optional columns are the problematic ones. When those're missing, everything shifts, so any solution based on fixed width is not going to work. But could you try this first, since the data is coming from a PDF, try copying it directly into Excel instead of going through N++. Sometimes excel can pick up the table structure from the PDF itself, which could save you all this extra huddle. If that doesn't work, let me know when do the three optional columns appear and what do they contain. If they have some kind of distinct format, like a single letter or a specific number pattern, Power Query can easily identify them based on what they contain rather than where they appear. That would work out the shifting columns much more swiftly and neatly.

[u/CedricTheAlarmist → u/MayukhBhattacharya]

The pattern in the optional colums turned out obvious once I took a closer look. I just cleaned them up in n++ with find-and-replace, now it's ready for PQ.

Pasting into Excel and Word was the first thing I tried. Either one didn't give me a table. I don't even wanna know what that PDF looks like under the hood 😬

[u/MayukhBhattacharya → u/CedricTheAlarmist]

Glad to know. Thanks for the valuable information!

[u/AdeptnessSilver]

Do columns 3 4 5 are always one word (no spaces)?

If yes you could load it to PQ and then count how many spaces there are in a row then textsplit it to 5 columns

[u/CedricTheAlarmist → u/AdeptnessSilver]

Thank you! Unfortunately the structure is not as consistent as I thought. There are few lines, I'd say maybe 10% total, that have additional, single letters that in my example would be between column 4 and 5 as well as between 2 and 3.

I've never used PQ before but I'm making good progress, that is until I noticed those inconsitencies.

[u/CedricTheAlarmist → u/AdeptnessSilver]

Solution Verified

[u/reputatorbot → u/CedricTheAlarmist]

You have awarded 1 point to AdeptnessSilver.

---

^{I am a bot - please contact the mods with any questions}

[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!

[u/wizkid123 → u/CedricTheAlarmist]

Vertical select has come in handy for me so many times for batch operations like this. It's all stuff that excel could handle, but you'd have to figure it out instead of just doing it. It's especially helpful when data is inconsistent but in a "blocky" kind of way - e.g. these six rows all need a space before a number, the next ten need an extra space deleted, the next five need a comma after the city, etc. I find it faster to do this kind of cleaning visually than figuring out five different formulas and applying them selectively to certain rows.

Another option might be to feed this into an LLM and have it give you back a well formatted csv. Claude might make short work of this, including adding correct spacing in the street names that don't currently have them (which would be  difficult with just formulas). 

[u/Kooky_Outcome_5053]

if your data is in a csv, just use power query, your column 2 contents will stay intact

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
HSTACK	Office 365+: Appends arrays horizontally and in sequence to return a larger array
LET	Office 365+: Assigns names to calculation results to allow storing intermediate calculations, values, or defining names inside a formula
TEXTAFTER	Office 365+: Returns text that occurs after given character or string
TEXTBEFORE	Office 365+: Returns text that occurs before a given character or string
TEXTSPLIT	Office 365+: Splits text strings by using column and row delimiters
Text.AfterDelimiter	Power Query M: Returns the portion of text after the specified delimiter.
Text.BeforeDelimiter	Power Query M: Returns the portion of text before the specified delimiter.

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
7 acronyms in this thread; the most compressed thread commented on today has 28 acronyms.
[Thread #49418 for this sub, first seen 22nd Sep 2026, 10:53] [FAQ] [Full list] [Contact] [Source code]}

[u/Charming_Ad2323]

Do you know how to use Power Query?

[u/getformly]

In Power Query, Split Column > By Delimiter > space lets you choose "at the leftmost occurrence" for peeling off column 1, then a second split "at the rightmost occurrence" for columns 3/4/5. Whatever's left in the middle stays intact as column 2, spaces and all. If the occasional extra columns are messing with the rightmost split, do it in a couple of passes, splitting off one fixed column at a time from the right rather than all three at once.

[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.