Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Mar 2006
    Location
    Kitchener, Ontario, Canada
    Posts
    53
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Removing a space (Excel 2000)

    Hi
    I have a column of text data in this format xxxx xxxx that I want in this format xxxxxxxx.
    Basically I want to remove the space between the two sets of 4 characters.
    I've tried substitute, clean and trim functions.
    No luck.
    Can anyone tell me how to remove this space?

    Thanks

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Removing a space (Excel 2000)

    Hi there

    Assuming the space is the fourth character in the string and currently in A1 you can use


    =IF(FIND(" ",A1)=4,SUBSTITUTE(A1," ","",1),A1)
    Jerry

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Removing a space (Excel 2000)

    If there are no other spaces in the data, you can select the column, then select Edit | Replace, enter a single space in the Find What box, leave the Replace With box empty, and click Replace All.
    If you prefer using formulas: let's say the data are in column A, starting in A1.
    You can use the formula
    <code>
    =SUBSTITUTE(A1," ","")
    </code>
    or
    <code>
    =LEFT(A1,4)&RIGHT(A1,4)
    </code>
    in B1 and fill down as far as needed.

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Removing a space (Excel 2000)

    In addition to what Hans has said, I will add one other comment. Since you say you have already tried Substitute, you need to verify that what looks like a blank really is a blank and not one of the none displaying character codes like a non-breaking space (frequently found in data copied from the internet). This formula will give you the character code for the fifth character in the string in cell A1:

    <code>
    =CODE(MID(A1,5,1))
    </code>

    If that does not return 32, then that character is not a blank. You can then use CHR(n) in the Substitute to get rid of it, where n is the number returned by the formula above.

    You can also use a formula like:

    <code>
    =LEFT(A1,4)&RIGHT(A1,4)
    </code>

    to get the first and last 4 characters, no matter what is in the middle.

    If you are doing this in VBA, then you can use code like this:

    <code>
    Public Sub RemoveBlank()
    Dim oCell As Range
    For Each oCell In Range("A1:A1000")
    oCell.Value = Left(oCell.Value, 4) & Right(oCell.Value, 4)
    Next oCell
    End Sub
    </code>
    Legare Coleman

Posting Permissions

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