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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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!
Bill PrewCommented:
So what data are you after in the content, just the line after the "billing address:" header, or other data too?

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

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:
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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.