Strange behavior of InStrRev() function

W

Windowed

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
 
T

Tom Ogilvy

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

Windowed

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 said:
The results you show would be what you would get if you used Instr rather
than InstrREV

--
Regards,
Tom Ogilvy

Windowed said:
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
Click to expand...
Click to expand...
 
Top