Results 1 to 5 of 5

delete ALL spaces in text string

This is a discussion on delete ALL spaces in text string within the Excel Questions forums, part of the Question Forums category; Hi, I've been trying to remove all spaces from a text string, but can't get it to work. The TRIM() ...

  1. #1
    New Member
    Join Date
    Jun 2002
    Posts
    15

    Default delete ALL spaces in text string

    Hi,

    I've been trying to remove all spaces from a text string, but can't get it to work. The TRIM() function only removes some spaces, but not all...

    Problem:
    I paste info from another application into Excel in the following format.
    e.g. "100 000.00" (could also be 100 000 000.10, or many other combos).
    This is interpreted as text in Excel, since there are spaces hard coded in between the numbers.

    Goal:
    Have Excel interpret this as a number. (e.g. "100000").

    For some reason I can't get the (text) substitute function to work (e.g. substitute(A1," ", "").

    I am using Excel 97. I don't mind using VBA, but would prefer not to.

    Any help would be appreciated.

    /Carl

  2. #2
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    67,768

    Default Re: delete ALL spaces in text string

    The formula:

    =VALUE(SUBSTITUTE(A1," ",""))

    worked for me.

  3. #3
    New Member
    Join Date
    Jun 2002
    Posts
    15

    Default Re: delete ALL spaces in text string

    The formula:

    =VALUE(SUBSTITUTE(A1," ",""))

    worked for me.
    At first I was going to write "fine for you, but it doesn't work for me", but after experimenting a bit I found out that it works for me if I type in
    "100 000.00" and runs the formula for that cell. However, it doesn't work for the data I have pasted (from the application)!

    Is there another character that looks like a space? How can I figure out which one it is (and remove it)?

    /Carl

  4. #4
    Board Regular goblin's Avatar
    Join Date
    Apr 2003
    Location
    Reykjavik
    Posts
    469

    Default Re: delete ALL spaces in text string

    It could be that the cells containing the data is formatted to display spaces but doesn't actually have any...

    If you pasted the data from another application, that isn't unlikely.

  5. #5
    MrExcel MVP Richie(UK)'s Avatar
    Join Date
    May 2002
    Location
    UK
    Posts
    3,330

    Default Re: delete ALL spaces in text string

    This thread is now being continued here:

    http://www.mrexcel.com/board2/viewtopic.php?t=55676
    Richie

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
  •  


DMCA.com