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)

SOLUTION

Dan Craciun

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Joe Howard

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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.

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

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

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

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

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

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

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.

Gustav Brock

Or simply:

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

/gustav