Finding 1st NonBlank Char in String

[Cyberdude]

I'm looking for a super simple technique for finding the first non-blank character in a string. I can do it with the following code:

Sub GetLoc()
Dim Strg As String, N As Integer, Loc As Integer
Strg = " ABC"
For N=1 To Len(Strg)
If Mid(Strg, N, 1) <> " " Then Exit For
Next N
Loc = N
End Sub

But this seems too "bulky". Surely there is a function that will do the job in one line ... something like InStr or Search. I thought that looking for the first occurrence of two characters, blank and a wildcard for nonblank, might be the key, but I can't seem to figure out what could represent a nonblank character. Any ideas? :banghead:

[Jacob Hilderbrand]

How about this:



Option Explicit

Sub GetLoc()
Dim Str As String
Dim Loc As Long
Dim Char As String
Str = " ABC"
Char = Left(Replace(Str, " ", ""), 1)
Loc = InStr(1, Str, Char)
MsgBox Loc
End Sub


Or as a cell formula:


=FIND(LEFT(SUBSTITUTE(A1," ",""),1),A1,1)

[TonyJollans]

Or ..


Left(Trim(A1),1)

In a cell or in VBA (cell needs = in front, VBA needs a proper cell reference - and could use LTrim but you get the idea)

This will get the first character - you need to wrap it in Instr (VBA) or FIND (Formula) to get the location of course.

[mvidas]

Along the same lines..


MsgBox Len(Strg) - Len(LTrim(Strg)) + 1

Matt