+ Reply to Thread
Results 1 to 9 of 9

replace multiple spaces with one space to parse text

  1. #1
    Registered User
    Join Date
    10-22-2013
    Location
    NYC
    MS-Off Ver
    Excel 2007
    Posts
    14

    replace multiple spaces with one space to parse text

    Hi

    I have a text file that is not formatted, and it has varying amounts of spaces between fields. I want to replace all "multiple spaces" with one space to convert the file to a delimited file.

    BUT, sometimes, there is a space between words that should not be seperated into multiple fields and should be in the same field. I guess I need a delimiter to seperate the fields

    If unclear, I can post a sample.

    Thanks!
    Last edited by goodmma; 11-20-2013 at 06:24 PM.

  2. #2
    Forum Expert Jakobshavn's Avatar
    Join Date
    08-17-2012
    Location
    Lakehurst, NJ, USA
    MS-Off Ver
    Excel 2007
    Posts
    1,970

    Re: replace multiple spaces with one space to parse text

    Post an example.

    Also review the TRIM() worksheet function in Excel Help.
    Gary's Student

  3. #3
    Registered User
    Join Date
    10-22-2013
    Location
    NYC
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: replace multiple spaces with one space to parse text

    the trim would only work on the spaces AFTER the last non-blank character, not the in-between spaces.

    attached is an example file...its messy
    Attached Files Attached Files

  4. #4
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: replace multiple spaces with one space to parse text

    trim() removes spaces in the middle as well
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  5. #5
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: replace multiple spaces with one space to parse text

    The macro at this website will remove all leading/trailing
    and multiple interspersed char 32 space characters.
    It will also remove and/or convert char 160 non breaking
    spaces into standard char 32 space characters. It will
    work on text or numbers and the numbers will be
    converted to true numeric numbers.

    I use this macro dozens of times every single day!
    It's a real time saver.

    http://www.mvps.org/dmcritchie/excel/join.htm#trimall
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  6. #6
    Registered User
    Join Date
    10-22-2013
    Location
    NYC
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: replace multiple spaces with one space to parse text

    Thanks Tony, but I am not that familiar with VBA or Macros...I can try to download the macro, import it, and run it.

    is there a formula I could use instead?

  7. #7
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: replace multiple spaces with one space to parse text

    As others have suggested, the TRIM function will remove the spaces.

  8. #8
    Valued Forum Contributor
    Join Date
    10-29-2012
    Location
    Mojokerto,Indonesia
    MS-Off Ver
    Excel 2007
    Posts
    554

    Re: replace multiple spaces with one space to parse text

    =TRIM(SUBSTITUTE(A1,CHAR(32)," "))

    copy down

  9. #9
    Registered User
    Join Date
    10-22-2013
    Location
    NYC
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: replace multiple spaces with one space to parse text

    Right, but then how do I have excell treat MICKEY MOUSE as one Field when I try to text to columns with a space as a delimiter?

  10. #10
    Registered User
    Join Date
    10-22-2013
    Location
    NYC
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: replace multiple spaces with one space to parse text

    ok Ghozi..seems I replied too late. I will give your suggestion a try
    Thanks everyone!

  11. #11
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: replace multiple spaces with one space to parse text

    well try find replace first find space space replace with say _
    then find space replace with say ^
    now text to columns delimited _ as delimiter treat consecutive delimiters as one /finish
    now find replace ^ with space
    there may be leading or trailing spaces at this point
    just select each column in turn then text to columns fixed width click finish takes about 3 mins to do

  12. #12
    Registered User
    Join Date
    10-22-2013
    Location
    NYC
    MS-Off Ver
    Excel 2007
    Posts
    14

    Re: replace multiple spaces with one space to parse text

    Got it to work by using fixed width instead of delimiter on the data.

    Thanks again!!

  13. #13
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: replace multiple spaces with one space to parse text

    forum playing double post games again
    Last edited by martindwilson; 11-20-2013 at 06:24 PM.

+ 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. Macro to Parse Text - Import text to Excel from Multiple Text Files & Folders
    By Novice_To_Excel in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-02-2012, 01:05 AM
  2. Parse Strings of Space-Separated Text of Varying Lengths
    By mlexcelhelpforum in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-09-2011, 07:49 AM
  3. Replies: 1
    Last Post: 05-05-2010, 09:10 PM
  4. Excel: replace all 2 spaces to FF/LR in column of text
    By Bill Craig in forum Excel General
    Replies: 4
    Last Post: 06-07-2006, 12:45 PM
  5. How can I replace spaces in text cells (excel) with underscores?
    By JB2006 in forum Excel - New Users/Basics
    Replies: 2
    Last Post: 04-20-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