Results 1 to 16 of 16

Replace multiple instances of spaces with a single delimiter

Threaded View

  1. #1
    Forum Contributor
    Join Date
    06-25-2013
    Location
    Longview, TX
    MS-Off Ver
    MS365
    Posts
    338

    Replace multiple instances of spaces with a single delimiter

    Hello there!

    I'm working on the beginning steps of a macro for a co-worker but first need to get the data in a format that I can work with. When the data comes into Excel (I'm using Excel 2010) it's all in 1 column instead of spread out over several columns like it should be. There are no tabs or commas used as delimiters; it's all separated by random numbers of spaces! So, I can't use delimiters in a Text-To-Columns. Of course each row of data might need to be spread across 2, 3, 4 or more columns, so I can't use fixed width, either.

    So, my next thought was to replace any instance of more than one space with a tab (or comma), and THEN do Text-To-Columns. But I can't figure out how to do that. I've been googling around and found something that looked promising, but it was from 9 years ago and it doesn't seem to want to work for me.
    What it says to do is: in "Find what", enter a space followed by {2,}, then in "Replace with" put in ^t. Here's the problem - next it wants me to check "Use wildcards" but I don't seem to have that option anywhere! (That's probably one more useful thing they eliminated when they "upgraded" Excel. Wish they'd leave stuff alone!)

    I suppose it's possible to create a macro without having to parse the data out across columns, but I can't imagine how that would go! I'll attempt to attach a workbook showing the raw data in its original form and how we want it to ultimately turn out.

    (If someone can just help me get the data parsed out correctly I THINK I can work through the rest of it over time. Unless there's some way to achieve the final desired result that skips the Text-To-Columns business)

    Thanks!

    Jenny
    Attached Files Attached Files
    Last edited by zookeepertx; 05-21-2018 at 05:00 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Remove consecutive spaces and replace with single comma
    By maacmaac in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-11-2015, 05:20 PM
  2. [SOLVED] import text file using multiple spaces as delimiter
    By Kaden265 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-03-2014, 11:50 AM
  3. [SOLVED] Calling multiple instances of a single userform
    By Sorjas in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-29-2014, 08:47 AM
  4. Replace white spaces with tab delimiter to creare table
    By thadacto in forum Word Programming / VBA / Macros
    Replies: 4
    Last Post: 12-01-2013, 03:56 PM
  5. Multiple Instances to Single Instance
    By jcholla in forum Excel General
    Replies: 2
    Last Post: 11-16-2011, 04:22 PM
  6. Reading text files into excel with multiple spaces as delimiter
    By XLBob in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-06-2009, 10:05 AM
  7. Find Multiple instances of Single Criterion in Row & Return To a Single Col
    By Sam via OfficeKB.com in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 05-09-2006, 10:10 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1