Counting spaces before a string in Excel

Question

The program exporting to Excel creates an indented file in one column:

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

How to create a function in excel that counts the number of spaces before a line of text?

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

It is advisable to look for a solution other than VBA.

Answer 1

TRIM doesn't help here, as it removes double spaces between words.

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

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

Answer 2

Try the following:

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

1, .

Answer 3

Ms Excel:

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

, . /, .

Answer 4

http://www.mrexcel.com/forum/excel-questions/61485-counting-spaces.html :

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

Cell - ( A1, B1, D3 ..).

:

B8: =LEN(F8)-LEN(SUBSTITUTE(F8," ",""))

F8: [ this is a test    ]

produces 4 in B8.

, , . , . , , 6.

Answer 5

, TRIM SUBSTITUTE, .

:

=MATCH(TRUE,MID(A1,COLUMN($A$1:$J$1),1)<>" ",0)-1

, .. Ctrl - Shift - Enter Enter.

10 , $J !

Answer 6

. 5 "_____" (5 ), 5, 4 ..

=IF(LEFT(B1,5)="     ",5,IF(LEFT(B1,4)="    ",4,IF(LEFT(B1,3)="   ",3,IF(LEFT(B1,2)="  ",2,1))))

Answer 7

LEN + TRIM , :

=LEN(Cell)-LEN(TRIM(Cell))