Results 1 to 3 of 3

Thread: CLEANING A CELL

  1. #1
    3 Star Lounger
    Join Date
    Jun 2005
    Posts
    386
    Thanks
    3
    Thanked 0 Times in 0 Posts
    How can you clean the spaces and apostrophe before a word in a cell and just leave the word or characters?

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    If you only want to strip all of the spaces and apostrophes from a cell you can use the substitute function.

    =substitute(substitute("A1"," ","'),",","")

    If you want to strip the leading spaces, trailing and incidents where there are more than two spaces between word you can use the Trim function:
    =trim(A1)

  3. #3
    Lounger
    Join Date
    Mar 2009
    Posts
    30
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Mike,
    Couldn't get yours to work pasting it into my worksheet, but based upon it
    =SUBSTITUTE(SUBSTITUTE(A1," ",""),"'","")

Posting Permissions

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