InStr looking for spaces does not give the expected result

Michael Paravicini
Michael Paravicini used Ask the Experts™
I am getting old but somehow the following code does not yield the desired result:

xName = "Steven Murray Thomas"
MsgBox InStr(1,xName," ")

gives zero and not the expected 7. Any help why?
Thank you so much Michael
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2015
Distinguished Expert 2018

No. It works fine for me - with a direct copy-paste - returns 7.
President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012
When any type of string operation fails, two things should come to mind:

1. Your not dealing with spaces.  This usually happens when text is the result of a cut and paste.

2. Your option compare statement in the module.

 Did you type that or cut and paste?

 Also, do you have an Option Explicit at the top of your module (you may have gotten the variable name wrong).

Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
as Jim mentioned these might not be spaces.

create a function which you can pass a value to which then prints out the ascii value associated with each character in the string.
Private Function TextToAscii(varValue as variant) as String

    Dim intLoop
    Dim strAscii as string

    if isnull(varValue) then 
        TextToAscii = "NULL"
        for intLoop = 1 to len(varValue)
            strAscii = ", " & asc(mid(varValue, intLoop, 1))
        TextToAscii = Mid(strAscii, 3)
    end if
end function

Open in new window

Then you can call this function from the immediate window, something like:

Call TextToAscii(forms!yourFormName.txtName)

or, if you put a break in your code, you could then call it like:

Call TextToAscii(xName)

if you
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Or simply put this statement and see what number it returns. If it doesn't return 32, that means the white space is not the space character.

MsgBox Asc(Mid(xName, 7, 1))

Open in new window

Most Valuable Expert 2015
Distinguished Expert 2018

Or just delete your old code and copy-paste your own code here at top … that was what I did.


Thank you so much to all of you. It was indeed as Jim said. It as not a space but a ASCI 160 - I now simply replaced it with CHR(32) and it works fine! Thank you.. Cheers Michael

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial