Space between numbers

Solved
Hello,

I have a column with several phone numbers that are written with spaces like this:
02 96 03 20 14
03 30 01 09 15
...
...
...
etc

how could I remove the spaces from all the cells
thank you

Configuration: Windows XP / Internet Explorer 6.0

6 answers

  • Votes
  • Date
  • Descending date
  1. Moderator
    Hello,

    You select your column, then Ctrl H
    Find: you type a space
    Replace with: nothing

    But it might just be a format??

    m@rina
    --
    - "On the office forum, questions are asked about office automation..."
    - "Oh really???"
    1. Thank you very much.
  2. Hello

    Select the entire column, and change the format/cell/number
    You must have chosen a custom format that is not suitable.

    Look for, or create a format like ####...## (10 times #)

    See you!
    Nyctaclope
    1. Hi,

      Removing spaces is not a problem since it is a special phone format that can simply be changed to standard format in Cell Format.
      The problem that will arise is the 0 that starts your number unless you write a custom format

      0#########

      Best,
      Mike-31

      A period of failure is a perfect time to sow the seeds of knowledge.
      1. Contributor
        Hello
        without saying which software you are using, it's not going to be easy

        In <Excel, there are two cases:
        either your cell is formatted as a phone number and you can reformat it to standard, you'll lose the leading 0, or you can use this formula:
        ="0"&A1

        or your cell is text with spaces, and you can remove them with this formula:
        =SUBSTITUTE(A1;" ";)

        by adjusting the height of the field.
        you can then copy this modified field and do a special paste (right click) as values to remove the formulas

        For your information, the function replacing a space with nothing also removes the leading 0

        best regards
        Let’s ask ourselves if we are not alone in understanding what we are explaining?
        1. Thank you, it worked in Excel format,
          but in CSV format it doesn't work after saving,
          when I reopen the file afterwards, I can't find the 0 that is placed at the beginning, but the space has been removed.
          Thank you for your help.
          1. Re,

            why are you saving the file in CSV format?
        2. You press ctrl F to start a search, then you have a tab, replace, you put a space in search, and in replace by: well you put nothing at all. And you click on replace all, and you won't have any spaces left on your page ^^ click on "resolved" if it's good!!!

          Good luck to you, let me know if you have any other issues with Excel! :p

          See you!
          --
          Need a search? Google is our friend, keep that in mind.