Solved Search last word in a string (1 Viewer)

[nirmal]

I am trying to get the last word in a string with multiple commas.
I do get the expected first word by the query "FirstWord: Left([Place],InStr([Place],", ")-1)"
I apply the similar to get the last word using "LastWord: Right([Place],InStr([Place],", ")-1)"
Now here I get weird results of last word.
Please open the query in the attachment.
Please help me with this

 

[arnelgp]

for last word, use InstrRev() instead of Instr()

LastWord: Mid([Place],InStrRev([Place],", ")+1)

 

[Ranman256]

i = instrRev([field],",")
if i >0 then
   sLastWord = mid([field],i+1)
else
   sLastWord = [field]
endif

 

[nirmal]

for last word, use InstrRev() instead of Instr()

LastWord: Mid([Place],InStrRev([Place],", ")+1)

Thank You Sir, Got the expected result
I have another doubt.
In the string below
"Bhor, Shiroda, Vengurla, Sindhudurg"
what would be code to search the second or third word

 

[arnelgp]

you create a function in a Module:

public function subStr(byval p as variant, byval pos as integer, optional byval delim as string=",") as string
dim var
p = p & ""
if len(p) = 0 then
    exit function
end if
var = split(p, delim)
if pos > ubound(var)+1 then
else
subStr = Trim$(var(pos-1))
end if
end function

on your query, call the function:

select place [place], substr([place], 1, ",") as firstPlace, substr([place], , ",") as secondPlace, substr([place],3 , ",") as thirdPlace from place_tbl;

 

[Eugene-LS]

In the string below
"Bhor, Shiroda, Vengurla, Sindhudurg"
what would be code to search the second or third word

for this I use the function below:

Public Function SplitStringPos(vString, iPosNo As Integer) As Variant
'Splitting a string
'Returns a substring after division standing in the iPosNo position
'----------------------------------------------------------------------------------------------
Const sSeparator$ = "," 'Separator
Dim vArr As Variant

On Error GoTo SplitStringPos_Err
    vArr = Split(vString & "", sSeparator)
    SplitStringPos = Trim(vArr(iPosNo - 1))

SplitStringPos_End:
    Exit Function

SplitStringPos_Err:
    SplitStringPos = "Error " & Err.Number
    Err.Clear
    Resume SplitStringPos_End
End Function

Example:

Dim sVal$
    sVal ="Bhor, Shiroda, Vengurla, Sindhudurg"
    Debug.Print SplitStringPos(sVal, 1)
    Debug.Print SplitStringPos(sVal, 2)
    Debug.Print SplitStringPos(sVal, 3)

Will return:

Bhor
Shiroda
Vengurla

... too late

 

[nirmal]

you create a function in a Module:

public function subStr(byval p as variant, byval pos as integer, optional byval delim as string=",") as string
dim var
p = p & ""
if len(p) = 0 then
    exit function
end if
var = split(p, delim)
if pos > ubound(var)+1 then
else
subStr = Trim$(var(pos-1))
end if
end function

on your query, call the function:

select place [place], substr([place], 1, ",") as firstPlace, substr([place], , ",") as secondPlace, substr([place],3 , ",") as thirdPlace from place_tbl;

Yes Sir got the result

But my last doubt is if there are strings like
"Abit Khind, Akola, Ahmadnagar"
"Bhor, Shiroda, Akola, Ahmadnagar"
"Akola, Ahmadnagar"
etc where there are multiple commas then

What will be the function to get "Akola" result in the above three samples

 

[MajP]

Public Sub test()

Dim x As String
x = "Abit Khind, Akola, Ahmadnagar"
Debug.Print subStr(x, 2)
x = "Bhor, Shiroda, Akola, Ahmadnagar"
Debug.Print subStr(x, 3)
x = "Akola, Ahmadnagar"
Debug.Print subStr(x, 1)

End Sub

Akola
Akola
Akola

The question is if that is correct for the first case for what you want.

 

[nirmal]

Public Sub test()

Dim x As String
x = "Abit Khind, Akola, Ahmadnagar"
Debug.Print subStr(x, 2)
x = "Bhor, Shiroda, Akola, Ahmadnagar"
Debug.Print subStr(x, 3)
x = "Akola, Ahmadnagar"
Debug.Print subStr(x, 1)

End Sub

Akola
Akola
Akola

The question is if that is correct for the first case for what you want.

Sir how can I get the second last values in my above question with different string lenghts
like I get first value by using query "FirstWord: Left([Place],InStr([Place],", ")-1)"
like wise how to get the second last values in below strings
"Abit Khind, Akola, Ahmadnagar"
"Bhor, Shiroda, Akola, Ahmadnagar"
"Akola, Ahmadnagar"

In short how th get the value before the last comma in every string

 

[nirmal]

for this I use the function below:

Public Function SplitStringPos(vString, iPosNo As Integer) As Variant
'Splitting a string
'Returns a substring after division standing in the iPosNo position
'----------------------------------------------------------------------------------------------
Const sSeparator$ = "," 'Separator
Dim vArr As Variant

On Error GoTo SplitStringPos_Err
    vArr = Split(vString & "", sSeparator)
    SplitStringPos = Trim(vArr(iPosNo - 1))

SplitStringPos_End:
    Exit Function

SplitStringPos_Err:
    SplitStringPos = "Error " & Err.Number
    Err.Clear
    Resume SplitStringPos_End
End Function

Example:

Dim sVal$
    sVal ="Bhor, Shiroda, Vengurla, Sindhudurg"
    Debug.Print SplitStringPos(sVal, 1)
    Debug.Print SplitStringPos(sVal, 2)
    Debug.Print SplitStringPos(sVal, 3)

Will return:

Bhor
Shiroda
Vengurla

... too late

Sir I require the second last values

 

[Eugene-LS]

Sir I require the second last values

Here you are ...

Public Function SecondLastValue(vString) As Variant
'Splitting a string and returns second last value of string
'----------------------------------------------------------------------------------------------
Const sSeparator$ = "," 'Separator
Dim vArr As Variant
Dim lVal As Long
On Error GoTo SecondLastValue_Err
    vArr = Split(vString & "", sSeparator)
    lVal = UBound(vArr) - 1
    If lVal < 0 Then lVal = 0
    
    SecondLastValue = Trim(vArr(lVal))

SecondLastValue_End:
    Exit Function

SecondLastValue_Err:
    SecondLastValue = "Error " & Err.Number
    Err.Clear
    Resume SecondLastValue_End
End Function

Example:

Private Sub test01()
    Debug.Print SecondLastValue("Abit Khind, Akola, Ahmadnagar")
    Debug.Print SecondLastValue("Bhor, Shiroda, Akola, Ahmadnagar")
    Debug.Print SecondLastValue("Akola, Ahmadnagar")
    Debug.Print SecondLastValue("Ahmadnagar")
    Debug.Print SecondLastValue(Null)
End Sub

Will return:

Akola
Akola
Akola
Ahmadnagar
Error 9

 

[nirmal]

Here you are ...

Public Function SecondLastValue(vString) As Variant
'Splitting a string and returns second last value of string
'----------------------------------------------------------------------------------------------
Const sSeparator$ = "," 'Separator
Dim vArr As Variant
Dim lVal As Long
On Error GoTo SecondLastValue_Err
    vArr = Split(vString & "", sSeparator)
    lVal = UBound(vArr) - 1
    If lVal < 0 Then lVal = 0
   
    SecondLastValue = Trim(vArr(lVal))

SecondLastValue_End:
    Exit Function

SecondLastValue_Err:
    SecondLastValue = "Error " & Err.Number
    Err.Clear
    Resume SecondLastValue_End
End Function

Example:

Private Sub test01()
    Debug.Print SecondLastValue("Abit Khind, Akola, Ahmadnagar")
    Debug.Print SecondLastValue("Bhor, Shiroda, Akola, Ahmadnagar")
    Debug.Print SecondLastValue("Akola, Ahmadnagar")
    Debug.Print SecondLastValue("Ahmadnagar")
    Debug.Print SecondLastValue(Null)
End Sub

Will return:

Akola
Akola
Akola
Ahmadnagar
Error 9

Sir, as I am trying to use the the above function in a expression, it is showing error
I tried this piece of expression
"SLV: SecondLastValue([Place])"

 

[Eugene-LS]

Sir, as I am trying to use the the above function in a expression, it is showing error
I tried this piece of expression
"SLV: SecondLastValue([Place])"

Check out please: The function must be located in an external module (not in module of form or report) ...

 

[nirmal]

Sir sending the sample database. The above function is in separate module.
Also Sir wanted the count of value obtained

 

[Eugene-LS]

Sir sending the sample database. The above function is in separate module.
Also Sir wanted the count of value obtained

Unfortunately I could not understand what your difficulties are
SQL Query:

SELECT Place, SecondLastValue([Place]) AS SLV FROM Place_tbl;

Will return:

 

[nirmal]

Sir I found that I had named the module with the same function name
Corrected it gave result.

Sir now how to get the count of the second last name

 

[SHANEMAC51]

What will be the function to get "Akola" result in the above three samples

Private Sub test01()
    Debug.Print SecondLastValue("Abit Khind, Akola, Ahmadnagar")
    Debug.Print SecondLastValue("Bhor, Shiroda, Akola,, Ahmadnagar")
    Debug.Print SecondLastValue("Akola, Ahmadnagar")
    Debug.Print SecondLastValue("Ahmadnagar")
    Debug.Print SecondLastValue(Null)
''Akola
''Null
''Akola
''Null
''Null
End Sub
Public Function SecondLastValue(vString) As Variant
'Splitting a string and returns second last value of string
'-------------------------------------------------------------------
Const sSeparator$ = "," 'Separator
Dim vArr As Variant
Dim lVal As Long
    vArr = Split(",," & vString, sSeparator)
    lVal = UBound(vArr) - 1
    SecondLastValue = Trim(vArr(lVal))
    If Len(SecondLastValue) = 0 Then SecondLastValue = Null
End Function

 

[Eugene-LS]

how to get the count of the second last name

SELECT
    SecondLastValue([Place]) AS SLV,
    Count(1) AS LCountRecords
FROM Place_tbl
GROUP BY SecondLastValue([Place]);

 

[Eugene-LS]

Private Sub test01()

BTW: How are Capi and Panurg doing there?

 

[Eugene-LS]

it's better to write about personal in the mail, it's inconvenient here - double translation distorts the text, and there are moderators here too

All right, you got it.