We help IT Professionals succeed at work.

Extract alpha numerics from string

413 Views
Last Modified: 2014-05-24
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)
Comment
Watch Question

Dan CraciunIT Consultant
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Dan CraciunIT Consultant
CERTIFIED EXPERT

Commented:
@MacroShadow: I think the OP wants to remove the "(BHP 83)" part. I thought he only wanted the numeric part, but upon rereading the question, I think he wants to delete those expressions.
If I'm correct, that regular expression will match all the test strings and the OP can use that to find/remove those strings.

Author

Commented:
I tried calling like this in query grid:
Expr1: regexpfind([concatenated],"\d+")

obviously i am missing something as it says error
Dan CraciunIT Consultant
CERTIFIED EXPERT

Commented:
I think you need VBScript to be able to use regular expressions in Access.

Author

Commented:
I tried the function Expr1: textnum([concatenated],True) 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

Author

Commented:
its a tricky one
Dan CraciunIT Consultant
CERTIFIED EXPERT

Commented:
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

Open in new window

Author

Commented:
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
Dan CraciunIT Consultant
CERTIFIED EXPERT

Commented:
If it does not have BHP inside then why should it be removed?
Please list all cases where you need text removed.

Author

Commented:
because i didnt spot that type earlier but the client omitted to put bhp within the brackets
Dan CraciunIT Consultant
CERTIFIED EXPERT

Commented:
So you're sure you want all numbers inside brackets to go?
Use this
\(?\d* ?BHP ?\d*\)?|\(\d+\)

Author

Commented:
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.

Author

Commented:
as opposed to making a super expression string for every client

Author

Commented:
I will try that new one one sec

Author

Commented:
worked a treat, I need another but i will post a different question rather than change question here.

Author

Commented:
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
Dan CraciunIT Consultant
CERTIFIED EXPERT

Commented:
If it works don't you think you should accept my solution? :)

Author

Commented:
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.
IT Consultant
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Dan CraciunIT Consultant
CERTIFIED EXPERT

Commented:
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.
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Or simply:

intBHP = Val(Replace(Replace(strBHP, "BHP", ""), "(", ""))

/gustav
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.