Convert excel formula to access (1 Viewer)

[HixonWolf]

Hi,

I have a formula in Excel that looks up the nth string after the comma.

=TRIM(MID([@Itm1],FIND("#",SUBSTITUTE([@Itm1],",","#",[@Itm3]))+1,255))

@Itm1 is the source data containing the strings and @Itm3 is the nth string to return.

Example string:

@Itm1:
690-10090,690-10090-A,619-6240,608-0319,659-0126,A860-0006-CUBB,860-0006,,,

@Itm3:
6

Would return 860-0006

is it possible to get this working in Access?

 

[Minty]

If the items are always separated by a comma you could use the split function

Public Function ParseCommaText(ByVal TextIn As String, X as Integer) As Variant

    On Error Resume Next
    
    Dim Var As Variant
    Var = Split(TextIn, ",", -1)
    ParseText = Var(X-1)


End Function

Then to use it

sMyString = ParseCommaText(Item1,Item3)

Assuming that your field names are Item1 and Item3 (definitely remove the @ and any other special symbols from those if they are field names, they will cause you a problem).

 

[HixonWolf]

Hi,

Thanks for your reply but I was hoping to avoid using VBA.

I found using something like:

Mid([Itm1], InStr([Itm1],",")+1)

would return the data after the first comma, but I would want it after the nth comma.

 

[Minty]

The Split() function isn't available in a query, unfortunately.
You might be able to do it with a Regular Expression, but my brain doesn't cope well with RegEx.

With the need to cope with a variable number for the nth word, I don't think you can do it without a small function.

 

[ebs17]

Your wish is unclear.
After the fifth comma is "A860-0006-CUBB", not only "860-0006"

 

[Pat Hartman]

Access is not Excel and you would do well to actually learn VBA. Minty gave you a good solution. You can call the function from any event procedure or use it in a query.

 

[HixonWolf]

Your wish is unclear.
After the fifth comma is "A860-0006-CUBB", not only "860-0006"

You're correct, it wasn't clear. I wish for it to return just the text between the commas.

I'm not sure where you get the fifth comma from though, I didn't give that as an example.

my example would return 860-0006 using the excel formula.

 

[HixonWolf]

Access is not Excel and you would do well to actually learn VBA. Minty gave you a good solution. You can call the function from any event procedure or use it in a query.

I understand that, and appreciate Minty's reply. Thanks for your input.

 

[Pat Hartman]

I just noticed your join date. Welcome

 

[cheekybuddha]

A VBA function is the way to go, because the direct translation would be something like:

=Trim(Left(Mid(Replace([Field1], ",", "#", 1, [Field3]), InStrRev(Replace([Field1], ",", "#", 1, [Field3]), "#") + 1), InStr(Mid(Replace([Field1], ",", "#", 1, [Field3]), InStrRev(Replace([Field1], ",", "#", 1, [Field3]), "#") + 1), ",") - 1))