Counting characters within a cell

[jacmorgra]

Hi, I am trying to count the number of spaces in a cell. I have a number codes of varying length in a cell which are separated by spaces - if I can count the spaces I will know how many codes there are.... I have been using text to columns with space as a delimiter but this is too cumbersome and wondered if the spaces could just be counted instead. Thanks in advance

 

[Peter_SSs]

Welcome to the MrExcel board!

A formula like this should tell you how many codes in A1

=LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1

 

[Fluff]

Hi & welcome to MrExcel.
How about

=LEN(A2)-LEN(SUBSTITUTE(A2," ",""))

 

[jacmorgra]

Welcome to the MrExcel board!

A formula like this should tell you how many codes in A1

=LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1

Brilliant - thankyou

 

[jacmorgra]

Brilliant - thankyou

Hi & welcome to MrExcel.
How about

=LEN(A2)-LEN(SUBSTITUTE(A2," ",""))

Brilliant - thank you

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[Sufiyan97]

Welcome to the MrExcel board!

A formula like this should tell you how many codes in A1

=LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1

Hello Peter,

What is the reason for putting +1 at the end?

 

[Peter_SSs]

Hello Peter,

What is the reason for putting +1 at the end?

To correctly count the number of codes. eg If there are 2 spaces (what the LEN parts of the formula counts) then there must be 3 codes.

 

[Sufiyan97]

To correctly count the number of codes. eg If there are 2 spaces (what the LEN parts of the formula counts) then there must be 3 codes.

Got it
Thank you! @Peter_SSs