Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Strange behavior of InStrRev() function
The results you show would be what you would get if you used Instr rather
than InstrREV -- Regards, Tom Ogilvy "Windowed" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Strange behavior of InStrRev() function
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" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Strange behavior | Excel Discussion (Misc queries) | |||
Strange behavior | Setting up and Configuration of Excel | |||
Strange behavior. | Excel Discussion (Misc queries) | |||
Strange macro behavior! | Excel Programming | |||
strange behavior with copyFromRecordset/ADO | Excel Programming |