View original (French)

Space between numbers

Solved
soha -  
 DZ -
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

  1.
    m@rina Posted messages 27617 Registration date   Status Moderator Last intervention   11 563
     
    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???"
    2
    1. DZ
       
      Thank you very much.
      0
  2. Nyctaclope Posted messages 5295 Registration date   Status Member Last intervention   1 254
     
    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
    0
  3. Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   5 147
     
    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.
    0
  4. Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
     
    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?
    0
  6. soha
     
    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.
    0
    1. Mike-31 Posted messages 18405 Registration date   Status Contributor Last intervention   5 147
       
      Re,

      why are you saving the file in CSV format?
      0
  7. Gawenronne Posted messages 295 Status Member 34
     
    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.
    -2