PeterBaileyUk
asked on
Extract alpha numerics from string
I am in access working with strings: the things i need to remove is bhp and its numeric value.
the bhp comes in different numerical values and different styles here is what i found so far
numerical part could come before or after the word bhp may also have brackets or not and the numeric part could be 1 to 3 digits.
maybe regexreplace could help find these "bhp footprints" but not sure how
ex:
122BHP
(90 BHP)
170 BHP
(90BHP)
(3 BHP)
(88BHP
(BHP 83)
the bhp comes in different numerical values and different styles here is what i found so far
numerical part could come before or after the word bhp may also have brackets or not and the numeric part could be 1 to 3 digits.
maybe regexreplace could help find these "bhp footprints" but not sure how
ex:
122BHP
(90 BHP)
170 BHP
(90BHP)
(3 BHP)
(88BHP
(BHP 83)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I tried calling like this in query grid:
Expr1: regexpfind([concatenated], "\d+")
obviously i am missing something as it says error
Expr1: regexpfind([concatenated],
obviously i am missing something as it says error
I think you need VBScript to be able to use regular expressions in Access.
ASKER
I tried the function Expr1: textnum([concatenated],Tru e) but its removing all numeric
100 AVANT =Avant which is wrong as it doesnt have the bhp footprint in it. 100 here is the model of the vehicle
80 2.0 115BHP = . . BHP
which is incorrect should be 80 2.0
or 80 2.0 (115) = should become =80 2.0
100 AVANT =Avant which is wrong as it doesnt have the bhp footprint in it. 100 here is the model of the vehicle
80 2.0 115BHP = . . BHP
which is incorrect should be 80 2.0
or 80 2.0 (115) = should become =80 2.0
ASKER
its a tricky one
No, you're using the wrong pattern. Try this:
Function removeBHP(ByVal txt As String) As String
With CreateObject("VBScript.RegExp")
.Pattern ="\(?\d* ?BHP ?\d*\)?"
.Global = True
removeBHP = .replace(txt, "")
End With
End Function
ASKER
very promising its almost there the final footprint that it didnt do:
A1 COMPETITION LINE TFSI (122) should be A1 COMPETITION LINE TFSI
its not explicitly down as bhp but within the brackets thats what it is saying
A1 COMPETITION LINE TFSI (122) should be A1 COMPETITION LINE TFSI
its not explicitly down as bhp but within the brackets thats what it is saying
If it does not have BHP inside then why should it be removed?
Please list all cases where you need text removed.
Please list all cases where you need text removed.
ASKER
because i didnt spot that type earlier but the client omitted to put bhp within the brackets
So you're sure you want all numbers inside brackets to go?
Use this
\(?\d* ?BHP ?\d*\)?|\(\d+\)
Use this
\(?\d* ?BHP ?\d*\)?|\(\d+\)
ASKER
This is only on one client maybe I should do it multi stage removal and store the pattern strings in a field for each client.
ASKER
as opposed to making a super expression string for every client
ASKER
I will try that new one one sec
ASKER
worked a treat, I need another but i will post a different question rather than change question here.
ASKER
I've requested that this question be closed as follows:
Accepted answer: 0 points for PeterBaileyUk's comment #a40082711
for the following reason:
great thank you
Accepted answer: 0 points for PeterBaileyUk's comment #a40082711
for the following reason:
great thank you
If it works don't you think you should accept my solution? :)
ASKER
I just found an oddity after elimination of the others.
A3 S LINE TDI 148
can it be told in this case to remove the numerical ie the 148 as here BUT only if it is not at the beginning of the string where the model will be.
A3 S LINE TDI 148
can it be told in this case to remove the numerical ie the 148 as here BUT only if it is not at the beginning of the string where the model will be.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
OP accepted wrong post.
Any news on when this bug will be fixed? I got several such mis-clicks since the new interface and I'm finding it hard to believe that all the askers are not paying attention.
Any news on when this bug will be fixed? I got several such mis-clicks since the new interface and I'm finding it hard to believe that all the askers are not paying attention.
Or simply:
intBHP = Val(Replace(Replace(strBHP , "BHP", ""), "(", ""))
/gustav
intBHP = Val(Replace(Replace(strBHP
/gustav
If I'm correct, that regular expression will match all the test strings and the OP can use that to find/remove those strings.