Strange behavior of InStrRev() function

I'm trying to parse a set of names in an Excel file and am getting some bad
results from the InStrRev() function. This is my code:

Public Sub extractLastName()

'intRow = 10
Dim intSpace As Integer 'number of spaces from end of whole name
to last space
Dim intLength As Integer

strWholeName = Cells(intRow, intCol)

intLength = Len(strWholeName)
'Debug.Print intLength
'Debug.Print strWholeName

intSpace = InStrRev(strWholeName, " ")
'Debug.Print intSpace

strLastName = Right(strWholeName, intSpace)
'Debug.Print strLastName

End Sub

The process works well for some names but not for others and I can't figure
out why. (The back results below are out or 20 rows--the others were OK.)

(Bad results examples below.) The problem arises with the InStrRev()
function. Using a step by step debugging everything is OK except that the
variable intSpace just comes out wrong on these cases. intLength is right as
is strWholeName. I tried retyping one or two of the names on the possibility
that there was some hidden character causing the problem, but that did not
help. [space] means the function is putting an extra space in front of the
last name that is not in the original field.


Virginia Foper nia Foper
Frank Paone [space]Paone
Pat Jessupq ssup
Barb Sorenson enson
Darryl J. Smith l J.Smith
David Sawinski winski
JoePennington, III [space]Pennington, III
Nina Flanigan nigan
Ramona Bridgeman idgeman
Cheryl Hodzen [space]Hodzen
Crystal MacKenzie-Zipp zie-Zipp

The results you show would be what you would get if you used Instr rather
than InstrREV

--
Regards,
Tom Ogilvy

"Windowed" <Windowed@discussions.microsoft.com> wrote in message
news:9DF66DDE-DD16-4894-A6ED-31BF8FEE643C@microsoft.com...
> I'm trying to parse a set of names in an Excel file and am getting some
bad
> results from the InStrRev() function. This is my code:
>
> Public Sub extractLastName()
>
> 'intRow = 10
> Dim intSpace As Integer 'number of spaces from end of whole name
> to last space
> Dim intLength As Integer
>
> strWholeName = Cells(intRow, intCol)
>
> intLength = Len(strWholeName)
> 'Debug.Print intLength
> 'Debug.Print strWholeName
>
> intSpace = InStrRev(strWholeName, " ")
> 'Debug.Print intSpace
>
> strLastName = Right(strWholeName, intSpace)
> 'Debug.Print strLastName
>
> End Sub
>
> The process works well for some names but not for others and I can't
figure
> out why. (The back results below are out or 20 rows--the others were OK.)
>
> (Bad results examples below.) The problem arises with the InStrRev()
> function. Using a step by step debugging everything is OK except that the
> variable intSpace just comes out wrong on these cases. intLength is right
as
> is strWholeName. I tried retyping one or two of the names on the
possibility
> that there was some hidden character causing the problem, but that did not
> help. [space] means the function is putting an extra space in front of the
> last name that is not in the original field.
>
>
> Virginia Foper nia Foper
> Frank Paone [space]Paone
> Pat Jessupq ssup
> Barb Sorenson enson
> Darryl J. Smith l J.Smith
> David Sawinski winski
> JoePennington, III [space]Pennington, III
> Nina Flanigan nigan
> Ramona Bridgeman idgeman
> Cheryl Hodzen [space]Hodzen
> Crystal MacKenzie-Zipp zie-Zipp

Or (I just figured this out): while it is searching from the end, the value
(intSpace) is the position relative to the beginning of the string. The
description of InStrRev that I was reading says "the position...counting from
the right side..." To me this means it's going to give me the number of
spaces from the end of the string. So the "right" output I got was merely
accidental and the wrong output was right.

"Tom Ogilvy" wrote:

> The results you show would be what you would get if you used Instr rather
> than InstrREV
>
> --
> Regards,
> Tom Ogilvy
>
> "Windowed" <Windowed@discussions.microsoft.com> wrote in message
> news:9DF66DDE-DD16-4894-A6ED-31BF8FEE643C@microsoft.com...
> > I'm trying to parse a set of names in an Excel file and am getting some
> bad
> > results from the InStrRev() function. This is my code:
> >
> > Public Sub extractLastName()
> >
> > 'intRow = 10
> > Dim intSpace As Integer 'number of spaces from end of whole name
> > to last space
> > Dim intLength As Integer
> >
> > strWholeName = Cells(intRow, intCol)
> >
> > intLength = Len(strWholeName)
> > 'Debug.Print intLength
> > 'Debug.Print strWholeName
> >
> > intSpace = InStrRev(strWholeName, " ")
> > 'Debug.Print intSpace
> >
> > strLastName = Right(strWholeName, intSpace)
> > 'Debug.Print strLastName
> >
> > End Sub
> >
> > The process works well for some names but not for others and I can't
> figure
> > out why. (The back results below are out or 20 rows--the others were OK.)
> >
> > (Bad results examples below.) The problem arises with the InStrRev()
> > function. Using a step by step debugging everything is OK except that the
> > variable intSpace just comes out wrong on these cases. intLength is right
> as
> > is strWholeName. I tried retyping one or two of the names on the
> possibility
> > that there was some hidden character causing the problem, but that did not
> > help. [space] means the function is putting an extra space in front of the
> > last name that is not in the original field.
> >
> >
> > Virginia Foper nia Foper
> > Frank Paone [space]Paone
> > Pat Jessupq ssup
> > Barb Sorenson enson
> > Darryl J. Smith l J.Smith
> > David Sawinski winski
> > JoePennington, III [space]Pennington, III
> > Nina Flanigan nigan
> > Ramona Bridgeman idgeman
> > Cheryl Hodzen [space]Hodzen
> > Crystal MacKenzie-Zipp zie-Zipp
>
>
>