CODEWITHSUNDEEP
excel
AME
AME

Reputation: 5300

Counting number of spaces before a string in Excel

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.

Upvotes: 5

Views: 28504

Answers (8)

Camilo Velasquez
Camilo Velasquez

Reputation: 163

If it is Indented you could create a Personal Function like this:

Function IndentLevel(Cell As Range)
'This function returns the indentation of a cell content
Application.Volatile
'With "Application.Volatile" you can make sure, that the function will be 
 recalculated once the worksheet is recalculated
'for example, when you press F9 (Windows) or press enter in a cell
IndentLevel = Cell.IndentLevel
'Return the IndentLevel
End Function

This will work only if it is Indented, you can see this property in the Cell Format -> Alignment.

After This you could see the Indentation Level.

enter image description here

Upvotes: 0

Gus
Gus

Reputation: 1

You almost got it with LEN + TRIM in answers before, you only need to combine both:

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

Upvotes: 0

Peter
Peter

Reputation: 1

Here's my solution. If the left 5 characters equals "_____" (5 blank spaces), then return 5, else look for 4 spaces, and so on.

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

Upvotes: 0

HIM
HIM

Reputation: 21

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.

Upvotes: 2

valex
valex

Reputation: 24144

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

Upvotes: 13

Peter Albert
Peter Albert

Reputation: 17495

As has been pointed out in the other answers, you can't really use TRIM or SUBSTITUTE as potential spaces in between words or at the end will give you the wrong result.

However, this formula will work:

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

You need to enter it as an array formula, i.e. press Ctrl-Shift-Enter instead of Enter.

In case you expect more than 10 spaces, replace the $J with a column letter further down in the alphabet!

Upvotes: 0

B.K.
B.K.

Reputation: 10162

As per http://www.mrexcel.com/forum/excel-questions/61485-counting-spaces.html, you may try:

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

where Cell is your target cell (i.e. A1, B1, D3, etc.).

My example:

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

F8: [ this is a test    ]

produces 4 in B8.

The above method will count spaces before the string if any were inserted, between individual words and after the string, if any were inserted. It won't count available space that does not have an actual white space character. So, if I inserted two spaces after test in the above example, the total count would be raised to 6.

Upvotes: 0

maroon
maroon

Reputation: 35

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.

Upvotes: 2

Related Questions