Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Delete all white space between the words in one cell

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I know that there is a TRIM function that delete extra space between words in a cell. How do you delete all space in a cell.
    ex. "White Space" == "WhiteSpace" Is there a function for it, that is similiar to TRIM function?
    Thank you.

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    Southfield,MI USA
    Posts
    2,308
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hiya,

    how about:

    =Substitute(A2," ","")

    Adam

  3. #3
    New Member
    Join Date
    Jun 2011
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Lightbulb Re: Delete all white space between the words in one cell

    =UPPER(CONCATENATE((TRIM(LEFT(A1,2))),(RIGHT(A1,5))))
    eg. ND 10552 = ND10552


  4. #4
    Board Regular Ruddles's Avatar
    Join Date
    Aug 2010
    Location
    Glevum Castra, Britannia
    Posts
    5,768
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Delete all white space between the words in one cell

    Quote Originally Posted by umvahed View Post
    =UPPER(CONCATENATE((TRIM(LEFT(A1,2))),(RIGHT(A1,5))))
    eg. ND 10552 = ND10552
    This only works if the data you want to keep is in the leftmost two and rightmost five positions in the string. It will turn "White Space" into "WHSPACE" which isn't exactly what Elisa was asking for.

    Also, why are you using UPPER?

    Still, she'll be deeply moved by the fact that we're still reading her posts after nine years!
    Last edited by Ruddles; Jun 23rd, 2011 at 08:43 AM.
    R.
    ---
    On two occasions I have been asked, 'Pray, Mr Babbage, if you put into the machine wrong figures, will the right answers come out?'
    I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.

  5. #5
    New Member
    Join Date
    Jun 2011
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Red face Re: Delete all white space between the words in one cell

    You can adjust the "2" to the desired length of your strings. Wont work for different lengths. Unless u use a LEN function somehow....lol ya, i saw it this year only!!

  6. #6
    New Member
    Join Date
    Sep 2011
    Posts
    25
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Talking Re: Delete all white space between the words in one cell

    Nice!! The Substitute formula worked perfectly!!
    Woohooo!!

  7. #7
    New Member
    Join Date
    Jul 2011
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up Re: Delete all white space between the words in one cell

    Perfect! The "Substitute" formula worked a treat!

  8. #8
    New Member
    Join Date
    Dec 2011
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Delete all white space between the words in one cell

    Substitute Formula Really waorks Thanks

  9. #9
    New Member
    Join Date
    Jan 2012
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Delete all white space between the words in one cell

    substitute is a great method
    Last edited by alansolars3h; Jan 10th, 2012 at 01:53 PM.

  10. #10
    New Member
    Join Date
    Apr 2013
    Location
    Philippines
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Thumbs up Re: Delete all white space between the words in one cell

    Quote Originally Posted by Asala42 View Post
    Hiya,

    how about:

    =Substitute(A2," ","")

    Adam


    Thank you for sharing this formula. it helps a lot.

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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