Count the number of spaces in Excel
Damien92
-
etudiant -
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
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
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
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
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 ^^
so my
potato
[space]cream
[space][space]bacon
becomes
potato
1cream
11bacon
By the way, rest assured, it's not for making a gratin ^^
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?
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?
to count the number of spaces in a cell: best regards
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