Formula to count the number of spaces before text/number is written in a cell.

Hi ALL,

I have data in a cell. For example in cell B2 I have the word Apple.
but before the word Apple is a space
In cell A2 I want the number 1. which is the number of spaces before apple.

Another example. In cell B3 I have the word A1234.
but before the word A1234 I have 10 spaces.
In cell B3 I would like the number 10.

I have attached an example.

thanks

Formula for number of spaces.xlsx

Hi ProtonLeah,

Thanks, that works a treat. however, if the cell is blank it return the number 0. what do I need to change in the formula so that when its a blank cell it returns nothing (i.e. no number 0)

Thanks

another option solution
=SUMPRODUCT((MID(B2,ROW($1:$100),1)=" ")*1)

Thanks Ghozi.

Originally Posted by Ace_XL

Format cells as 0;;""

not sure what you mean by this ACE_XL??