+ Reply to Thread
Results 1 to 10 of 10

Removing a double space before characters in a cell

  1. #1
    Registered User
    Join Date
    07-20-2011
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    4

    Removing a double space before characters in a cell

    Hi,
    Can anyone help...
    I have an excel spread sheet with over 7000 rows of data on (only 3 columns).

    For some reason, some 3500 of the key cells have a double space prior to the character which in turn is affecting the a-z sorting of the data. Excel is assuming that the double spcase is a character and sorting them differently to the rest of the sheet.

    Without manually going through the 3500 cells and deleting the double space, does anyone know how I can remove the double space before the characters so that it sorts correctly?

    (I've tried left and right align but it doesn't change the way it sorts...)

    Many thanks for any help you can give me.

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Removing a double space before characters in a cell

    Are there other spaces in those cells that you need to keep?

    Also, in an adjacent cell to one of those cells with preceding spaces, enter formula: =CODE(LEFT(A2))

    where A2 is the cell of interest. What does that return?
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Valued Forum Contributor
    Join Date
    11-11-2008
    Location
    Euro
    MS-Off Ver
    2007, 2010
    Posts
    470

    Re: Removing a double space before characters in a cell

    you can use replace by steps:

    select the range (the 3 column) containing the double space
    + Press Ctrl+H
    + in Find box, you type double space (" ")
    + in Replace box: you leave nothing / or what you want to replace double space
    + press Replace all

  4. #4
    Registered User
    Join Date
    07-20-2011
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Removing a double space before characters in a cell

    Hi NBVC,
    Yes, there are other spaces in the cell I need to keep. The content of the cell is a company name i.e. ABC 123 Limited therefore needing the spaces....
    I entered in the formula you gave me in the next column, refering the cell reference i.e. D18, D19 etc and it returned a result of 160 regardless of the amount of letters / words in the appriopriate cell.

    Any thoughts?

  5. #5
    Registered User
    Join Date
    07-20-2011
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Removing a double space before characters in a cell

    TigerTiger,
    Thanks, I just tried that and it doesn't find any space space with ot without the quote marks..." "

  6. #6
    Valued Forum Contributor
    Join Date
    11-11-2008
    Location
    Euro
    MS-Off Ver
    2007, 2010
    Posts
    470

    Re: Removing a double space before characters in a cell

    the result =160, so you try the formula
    =SUBSTITUTE(A1,CHAR(160),"")

    A1 - the cell contains the data

    and copy cross for other cells

  7. #7
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Removing a double space before characters in a cell

    Not sure that TRIM() or CLEAN() get rid of the CHAR(160)

    Try:

    =SUBSTITUTE(A1,CHAR(160),"")

    in an adjacent column where A1 is first text, copied down.

    Then you can copy and Paste Special|Values option over the original and delete the formula column.

  8. #8
    Registered User
    Join Date
    07-20-2011
    Location
    London
    MS-Off Ver
    Excel 2003
    Posts
    4

    Re: Removing a double space before characters in a cell

    Tigertiger,
    I entered =SUBSTITUTE(D2,CHAR(160),"") as you suggested and it copied over the content of the cell exactly as it was, i.e. with the double space in front. I also did it with a " " in the formula but that didn't work either, just threw up an exception....

    Thank you for helping but for whatever reason it still isn't doing the trick.... Any other ideas?

  9. #9
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,898

    Re: Removing a double space before characters in a cell

    Post a sample workbook with a few of those "bad" entries in it.

  10. #10
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: Removing a double space before characters in a cell

    How about:

    =RIGHT(D2,LEN(D2)-2)

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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