How do I find the first non blank character in a string

I have a table which is linked to an Outlook folder. The body of the email message is the string I am working with.  From various points in the string I need to know the position of the first non blank character.
John KincaidAsked:
Who is Participating?
John KincaidConnect With a Mentor Author Commented:
I code to positively identity the ascii character of the offending characters. Then I used the Access replace(instr,chr(13) & chr(13), chr(13)) which removed all of the white space and simplified the process.
Bill PrewCommented:
So, do you want to pass the "current location" in the string in, and then the function returns the next non-blank character?  If so, here's a small function you can use for this:

Function NextNonBlank(strText, intOffset) As Integer
   NextNonBlank = -1
   For i = intOffset + 1 To Len(strText)
      If Mid(strText, i, 1) <> " " Then
         NextNonBlank = i
         Exit For
      End If
   Next i
End Function

Open in new window

John KincaidAuthor Commented:

this is a sample of the text I am using:
A course has been purchased:

----- CART -----

    Course Date: 08/17/2013
    Course Time: 1:00 p.m. - 5:00 p.m.
    Book: Required - Available for purchase below
    Quantity: 1
    Price: $49.00
Subtotal: $49.00
Handling: $0.00
Order Total: $49.00

    Order #: 115079739
    Date: Fri 16 Aug 2013 09:56:31 AM CDT
    Card Number: xxxxxxxxxxx3003 (Amex)
    Order Total: $49.00

Billing Address:
    Xxxx Xxxxxx
    Xxxx X Xxxxxx, MD PA
    1111 Xxxxxxxx Avenue
    Dallas, TX 75225
    United States
    GeoIP Lookup: United States


This is my code:

Option Compare Database
Option Explicit

Sub TestStrings()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strBody As String
Dim intNameStart As Integer
Dim intNameEnd As Integer

  Set db = CurrentDb
  Set rs = db.OpenRecordset("Class Schedule")
  If rs.EOF Then GoTo CleanUp
  With rs
    Do Until .EOF
      strBody = .Fields("Contents")
      intNameStart = NextNonBlank(strBody, InStr(strBody, "Billing Address:") + 16)
'      intNameEnd = ??
  End With
  Set db = Nothing
End Sub

Function NextNonBlank(strText, intOffset) As Integer
Dim i As Integer

  NextNonBlank = -1
  For i = intOffset + 1 To Len(strText)
    If Mid(strText, i, 1) <> " " Then
      Debug.Print Mid(strText, i, 1)
      NextNonBlank = i
      Exit For
    End If
  Next i
End Function

The name begins with the first non blank character following "Billing Address:" After I get this I need to find the end char in order to extract the name. I hope I am going about this correctly.

Will be away until later this evening.  Thanks in advance!
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Bill PrewCommented:
So what data are you after in the content, just the line after the "billing address:" header, or other data too?

John KincaidAuthor Commented:

There are several bits of data I need. Class type. Class date. Class time. Student name. Phone. Home phone. Work phone. Email. Maybe another, I forget. There are three different messages depending upon the source. I was using "Billing Address" as a landmark for Student Name. So the next non blank character was to represent intNameStart. I will also need to work out intNameEnd.

John KincaidAuthor Commented:
Learning to ask the right questions is part of this process. I appreciate the help of all the members of experts exchange.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.