Avatar of PeterBaileyUk
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)
Microsoft AccessRegular Expressions

Avatar of undefined
Last Comment
Gustav Brock

8/22/2022 - Mon
SOLUTION
Dan Craciun

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
Joe Howard

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Dan Craciun

@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.
PeterBaileyUk

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

obviously i am missing something as it says error
Dan Craciun

I think you need VBScript to be able to use regular expressions in Access.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
PeterBaileyUk

ASKER
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
PeterBaileyUk

ASKER
its a tricky one
Dan Craciun

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

⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
PeterBaileyUk

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
Dan Craciun

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

ASKER
because i didnt spot that type earlier but the client omitted to put bhp within the brackets
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Dan Craciun

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

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.
PeterBaileyUk

ASKER
as opposed to making a super expression string for every client
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
PeterBaileyUk

ASKER
I will try that new one one sec
PeterBaileyUk

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

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
Your help has saved me hundreds of hours of internet surfing.
fblack61
Dan Craciun

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

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.
ASKER CERTIFIED SOLUTION
Dan Craciun

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Dan Craciun

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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Gustav Brock

Or simply:

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

/gustav