View original (French)

Count the number of spaces in Excel

Damien92 -  
 etudiant -
Hello,
I would like to count the number of spaces present in a cell in Excel.

Let me explain, if I have the following list:
potato
[space]cream
[space][space]bacon
etc...

I would like to display next to each line
0
1
2
etc...

Do you know a function that would allow me to do the counting for me?

Best regards,

Configuration: Windows XP / Firefox 9.0.1

3 answers

  1. Damien92
     
    Perfect!
    No, the goal wasn't to remove them.

    I create fairly complex equipment nomenclatures. The raw nomenclatures hierarchize the levels of my parts with spaces, that's why I wanted to replace the spaces with numerical values.

    I hadn't thought of combining several substitutes ;)
    Thanks for the tip.

    Damien
    1
    1. JvDo Posted messages 1924 Registration date   Status Member Last intervention   859
       
      Hello,

      to count the number of spaces in a cell: 
      =LEN(my_cell)-LEN(SUBSTITUTE(my_cell," ",""))
      best regards
      2
    2. chossette9 Posted messages 6855 Registration date   Status Contributor Last intervention   1 313
       
      +1 to JvDo!
      1
    3. JvDo Posted messages 1924 Registration date   Status Member Last intervention   859
       
      Thank you chossette9

      I don't remember from which Caesar I borrowed this formula. So +1 especially to all those on the forum who taught me what I know.

      Best regards
      0
  2. Damien92
     
    Due to the inability to count spaces, I used the Substitute function, which allows me to replace one character with another
    so my
    potato
    [space]cream
    [space][space]bacon

    becomes
    potato
    1cream
    11bacon

    By the way, rest assured, it's not for making a gratin ^^
    0
  3. Vaucluse Posted messages 27336 Registration date   Status Contributor Last intervention   6 453
     
    Hello, if it was to remove them, the simplest way was to select the field and
    ctrl + H in the replace window:
    at the top type a blank
    at the bottom enter nothing and "replace all"
    To know how many there were:
    a formula that shows you the number up to 7 at the top of the text:
    =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1;" ";"7";7);" ";"6";6);" ";"5";5);" ";"4";4);" ";"3";3);" ";"2";2);" ";"1";1)
    damnit
    Let's ask ourselves if we are not alone in understanding what is being explained?
    0
    1. etudiant
       
      Super, thank you very much Vaucluse, your formula works well.
      0