+ Reply to Thread
Results 1 to 3 of 3

Formula to replace single spaces only?

  1. #1
    Registered User
    Join Date
    04-24-2009
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    22

    Formula to replace single spaces only?

    Hi All,
    Is there a way to replace single spaces with another character in text strings, whilst ignoring spaces that are greater than a single space?

    I have a text file that I want to tabulate and the easiest way I can see would be to replace the single spaces, then trim the remaining spaces so that they can be used as the column separators.

    Thanks in advance,
    Matt

  2. #2
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Formula to replace single spaces only?

    the below would do what you're asking, I think

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    the above first swaps out the spaces in "multiple spaces" for ^ before replacing the remaining single spaces with | reverting the ^ back to space, and TRIMing.

    if you can't do with this formulae, or prefer not to, post back and outline the steps in your process so people can refine an approach for you.
    Last edited by XLent; 02-14-2019 at 10:32 AM. Reason: missed the final TRIM requirement

  3. #3
    Registered User
    Join Date
    04-24-2009
    Location
    England
    MS-Off Ver
    Excel 2003
    Posts
    22

    Re: Formula to replace single spaces only?

    Cheers XLent! Works perfectly and does exactly what I want it to.

    Was messing on for an age trying to work it out and now I see it written down it seems obvious.

    Thanks again,
    Matt

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replace multiple instances of spaces with a single delimiter
    By zookeepertx in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 05-25-2018, 10:47 AM
  2. [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
  3. [SOLVED] Replacing double spaces with single spaces
    By EdWoods in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 04-15-2013, 01:20 AM
  4. replace blank spaces in first row only
    By wkilc in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-14-2010, 09:02 PM
  5. Replace blanks/spaces
    By antony moseley in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-16-2009, 12:48 PM
  6. Replies: 1
    Last Post: 03-27-2006, 12:25 PM
  7. Replies: 3
    Last Post: 02-15-2006, 01: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