Removing text from string using Replace() & wildcards (1 Viewer)

[Thicko]

Hi Guys & Girls,

I have a list of consumables;

Syringe 50ml
Syringe 20ml
Syringe 5ml
Syringe Cap
White Needle

I want to remove only the number and the ml part from the list, so I would end up with;

Syringe
Syringe
Syringe
Syringe Cap
White Needle

If I use

Replace([DrugNameVial],"50ml","")

I get the desired result for the 50ml syringe size.

I have tried every possible combination of "**ml", "##ml", "Like [0-9]ml all with no success.

Does anybody know how this can be resolved without having to individual enter each syringe size "5ml", "20ml" etc

I can't even just take the text from the right till the first space as this would lead to problems with other consumables in the list.

Any insight much appreciated

 

[pr2-eugin]

You could write a function ?

Public Function ReplaceMeasurement(Expression) As String
[COLOR=Green]'********************
'Code Courtesy of
'  Paul Eugin
'********************[/COLOR]
    If IsNull(Expression) Then Exit Function
    
    Dim spacePos As Long, chkNum As String
    spacePos = InStr(Expression, " ")
    If spacePos <> 0 Then
        If IsNumeric(Mid$(Expression, spacePos + 1, 1)) Then
            ReplaceMeasurement = Trim(Mid$(Expression, 1, spacePos))
        Else
            ReplaceMeasurement = Expression
        End If
    Else
        ReplaceMeasurement = Expression
    End If
End Function

Tested against,

? ReplaceMeasurement("Syringe 50ml")
Syringe
? ReplaceMeasurement("Syringe 20ml")
Syringe
? ReplaceMeasurement("Syringe 5ml")
Syringe
? ReplaceMeasurement("Syringe Cap")
Syringe Cap
? ReplaceMeasurement("White Needle")
White Needle
? ReplaceMeasurement(Null)

 

[CJ_London]

I think if you modify Pauls function slightly

change

spacePos = InStr(Expression, " ")

to

spacePos = InStrRev(Expression, " ")

it will also cope with something like

White Needle 100mm

 

[pr2-eugin]

spacePos = InStrRev(Expression, " ")

it will also cope with something like

White Needle 100mm

That is a good point Chris !

 

[gemma-the-husky]

slightly different thought

if this is the start of a development - consider storing the attributes separately

ie
- product type - syringe
- product size - 50mm

then you do not need any code - your system does it all for you

 

[Thicko]

Many Thanks Paul & Chris, A brilliant solution to the problem.

Sadly Dave it's an old stock database we're trying to add a bit of additional functionality to.

Cheers