Counting number of spaces before a string in Excel

Question

A program that exports to Excel creates a file with an indented list in a single column like this:

Column A
 First Text
  Second Text
   Third Text
  Fourth Text
 Fifth Text

How can I create a function in excel that counts the number of white spaces before the string of text?

So as to return: 1 for the first text row and 3 for the for the thirst row, etc in this example.

Preferably seeking a non-VBA solution.

Answer 1

TRIM doesn't help here because it removes double spaces also between words.

The main idea is to find the FIRST letter in the trimmed string and find its position in the original string:

=FIND(LEFT(TRIM(A1),1),A1)-1

Answer 2

You can try this function in Ms Excel itself:

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

This would apply if the results are in a single cell. If it is for a whole row/column, just drag the formula accordingly.

Answer 3

Try below:

=FIND(" ",A1,1)-1

It calculates the position of the first found whitespace character in a cell and reduces it by 1 to reflect number of characters before that position.