Solved Search last word in a string

N

nirmal

Member
Local time
Today, 09:33
Joined
Feb 27, 2020
Messages
82
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
 

Attachments

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

LastWord: Mid([Place],InStrRev([Place],", ")+1)
 
Code: 
i = instrRev([field],",")
if i >0 then
   sLastWord = mid([field],i+1)
else
   sLastWord = [field]
endif
 
arnelgp said:
for last word, use InstrRev() instead of Instr()

LastWord: Mid([Place],InStrRev([Place],", ")+1)
Click to expand...
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
 
you create a function in a Module:
Code: 
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;
 
nirmal said:
In the string below
"Bhor, Shiroda, Vengurla, Sindhudurg"
what would be code to search the second or third word
Click to expand...
for this I use the function below:
Code: 
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:
Code: 
Dim sVal$
    sVal ="Bhor, Shiroda, Vengurla, Sindhudurg"
    Debug.Print SplitStringPos(sVal, 1)
    Debug.Print SplitStringPos(sVal, 2)
    Debug.Print SplitStringPos(sVal, 3)

Will return:
Code: 
Bhor
Shiroda
Vengurla

... too late :)
 
arnelgp said:
you create a function in a Module:
Code: 
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;
Click to expand...
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
 
Code: 
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.
 
MajP said:
Code: 
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.
Click to expand...
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
 
Eugene-LS said:
for this I use the function below:
Code: 
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:
Code: 
Dim sVal$
    sVal ="Bhor, Shiroda, Vengurla, Sindhudurg"
    Debug.Print SplitStringPos(sVal, 1)
    Debug.Print SplitStringPos(sVal, 2)
    Debug.Print SplitStringPos(sVal, 3)

Will return:
Code: 
Bhor
Shiroda
Vengurla

... too late :)
Click to expand...
Sir I require the second last values
 
nirmal said:
Sir I require the second last values
Click to expand...
Here you are ...
Code: 
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:
Code: 
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:
Code: 
Akola
Akola
Akola
Ahmadnagar
Error 9
 
Eugene-LS said:
Here you are ...
Code: 
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:
Code: 
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:
Code: 
Akola
Akola
Akola
Ahmadnagar
Error 9
Click to expand...
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])"
 
nirmal said:
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])"
Click to expand...
Check out please: The function must be located in an external module (not in module of form or report) ...
 

Attachments

  • Screenshot 2022-03-01 090318.png
    Screenshot 2022-03-01 090318.png
    199 KB · Views: 322
Sir sending the sample database. The above function is in separate module.
Also Sir wanted the count of value obtained
 

Attachments

nirmal said:
Sir sending the sample database. The above function is in separate module.
Also Sir wanted the count of value obtained
Click to expand...
Unfortunately I could not understand what your difficulties are
SQL Query:
SQL: 
SELECT Place, SecondLastValue([Place]) AS SLV FROM Place_tbl;
Will return:
 

Attachments

  • Screenshot 2022-03-01 111808.png
    Screenshot 2022-03-01 111808.png
    80.4 KB · Views: 299
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
 
nirmal said:
What will be the function to get "Akola" result in the above three samples
Click to expand...
Code: 
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
 
nirmal said:
how to get the count of the second last name
Click to expand...
SQL: 
SELECT
    SecondLastValue([Place]) AS SLV,
    Count(1) AS LCountRecords
FROM Place_tbl
GROUP BY SecondLastValue([Place]);
 
You must log in or register to reply here.

Similar threads

A
Problem with Word mail merge from query
Replies
8
Views
222
Al Kramer
A
R
I need a query that collects words from a column and turn it into a string for a forum. Can it be done?
Replies
11
Views
592
arnelgp
arnelgp
murray83
Solved duplicate letter in string
Replies
8
Views
379
moke123
moke123
P
Solved Left or Mid?
Replies
8
Views
203
prasadgov
P
S
Getting total in one line of the query
Replies
13
Views
244
silentwolf
S

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom