How to specify an empty space in search/find functions?

[cotatliad]

I am trying to use the Find/Search functions to determine where the empty space ends and where the actual characters begin. So for example "___Room" would have 3 spaces before actual characters (_ represents empty space, assume there is nothing there). How do I make search/find distinguish between empty space and characters? Thanks!!

 

[CWatts]

There are seveal ways. if you only have leading spaces at the beginning, and no trailing spaces at the end you can use the following function:

=LEN(A1)-LEN(TRIM(A1))+1

This tells you that the first non space is in the 9th spot.

You can also use a User Defined Function with the following syntax:

=GoodChar(A1)

And this is the code you'd need to use.

Function GoodChar(cl As Range) As Long
If cl.Rows.Count > 1 And cl.Columns.Count > 1 Then
    GoodChar = -1
Else
    GoodChar = 1
    Do Until Mid(cl, GoodChar, 1) <> " "
        GoodChar = GoodChar + 1
    Loop
End If
End Function

 

[cgcamal]

Hi cotatliad,

An option with sheet formula.

Excel Workbook
	A	B	C
1	Room	3	End of space character
2		4	Begin of first letter or number
...

Cell Formulas
Range	Formula
B1	=LEN(A1)-LEN(SUBSTITUTE(A1," ",""))
B2	=LEN(A1)-LEN(SUBSTITUTE(A1," ",""))+1

Regards.