Getting specific part out of a cell

[MarcovH]

Hi,



how do I extract the number of dates and number of percentages out of a cell?

I have for example: "within 10 days 5% discount".

In this it is always "days" and "discount". So I would expect something like 4 digits left of "days" and something like: 3 digits left of "discount".

Preferably in basic Access as I am new to Access.

[ranman256]

Sub BreakupLine()
Dim vDays, vPct


ParseWords "within 10 days 5% discount", vDays, vPct
MsgBox vDays, , "Days"
MsgBox vPct, , "percent"
End Sub


Sub ParseWords(ByVal pvWord, ByRef pvDays, ByRef pvPct)
pvWord = Mid(pvWord, 8)
i = InStr(pvWord, " ")
pvDays = Trim(Left(pvWord, i - 1))
pvWord = Mid(pvWord, i + 5)
i = InStr(pvWord, "%")
pvPct = Trim(Left(pvWord, i - 1))
End Sub

[knarfreppep]

Provided the string format is truly constant, this will e.g. give you the days -

Cint(Mid(Left("within 10 days 5% discount",InStr("within 10 days 5% discount","days")-2),InStr(Left("within 10 days 5% discount",InStr("within 10 days 5% discount","days")-2)," ")))

OR

Cint(Mid(Left(strString,InStr(strString,"days")-2),InStr(Left(strString,InStr(strString,"days")-2)," ")))

[MarcovH]

thanks knarfreppep, now I tried to copy over the 2nd solution and tried to convert it to the "5" in 5%, but no joy...

[June7]

Why no joy - error message, wrong results, nothing happens? Post the attempted expression.

[MarcovH]

sorry, but here you:
data: within 10 days 5 % discount
qry: Mid(Left([PaymentSettlement],InStr([PaymentSettlement],"%")-2),InStr(Left([PaymentSettlement],InStr([PaymentSettlement],"%")+2),"s"))
outcome: S 5

so at least I have overcome my #error, now I need to kick out the "s" in my outcome "s 5"

[June7]

Why do you have an 's' in the expression?

Try:
Val(Mid([PaymentSettlement], InStr([PaymentSettlement], "days") + 5))

Be aware these expressions will error if the field is null. If that is a possibility, handle nulls.

Val(Mid(Nz([PaymentSettlement], ""), InStr(Nz([PaymentSettlement], ""), "days") + 5))