little bit complicated

[mr moe]

Hi, I hope that some one can help. I have column that ends with (1232) how can i extract only the 1232 for example,moved car to blablabla(1232) I want to create a new column and insert only what's in Parentheses. thanks in advance.

 

[boblarson]

Does it always have parentheses?

If so you should be able to use:

MyNewName:Mid([YourFieldNameHere], InStr(1, [YourFieldNameHere], "(", vbTextCompare) + 1, InStrRev([YourFieldNameHere], ")", , vbTextCompare) - 1)

 

[mr moe]

I"m sorry but what is vbTextCompare? thanks

 

[boblarson]

Actually that is something to tell it how to compare but that is the incorrect way to do it in a query, but I forgot to remove it. So, modify to:

MyNewName:Mid([YourFieldNameHere], InStr(1, [YourFieldNameHere], "(") + 1, InStrRev([YourFieldNameHere], ")") - 1)

 

[mr moe]

Thank you so much, but it still shows the right ) example 123) i want to remove the right ) thanks.

 

[mr moe]

can someone help

can you guys help I still get the ")" at the end example (1234) i still get 1234) i want to only see 1234. Thanks.

 

[DALeffler]

Try:

MyNewName:Mid([YourFieldNameHere], InStr(1, [YourFieldNameHere], "(") + 1, InStrRev([YourFieldNameHere], ")") - InStr(1, [YourFieldNameHere], "("))

 

[boblarson]

Doug:

I tried yours and it didn't work for me, but I modified it slightly and it worked:


MyNewName:Mid([YourFieldNameHere], InStr(1, [YourFieldNameHere], "(") + 1, InStrRev([YourFieldNameHere], ")") - InStr(1, [YourFieldNameHere], "(")-1)

 

[DALeffler]

Oops!

Knew I shoulda tested that! Sorry...

MyNewName: Mid([YourFieldNameHere],InStr(1,[YourFieldNameHere],"(")+1,InStrRev([YourFieldNameHere],")")-(InStr(1,[YourFieldNameHere],"(")+1))

also works (watch them darn parentheses!)

 

[mr moe]

thanks Doug, Thanks everybody.

 

[mr moe]

Last Parentathies

thanks guys, there is only one iusse if a record has more than one Parentheses, i only want to extract the last Parentheses at the end of the line, example, abddkc(100),ddkkdkdk(900). I want to extract the 900 since it's at the end of the line. what is showing me now is 100),ddkkdkdk(900...
thanks.

 

[DALeffler]

InStr finds the specified character in a string by searching the string from left to right.

InStrRev searches from right to left, so:


MyNewName: Mid([YourFieldNameHere],InStrRev([YourFieldNameHere],"(")+1,InStrRev([YourFieldNameHere],")")-(InStrRev([YourFieldNameHere],"(")+1))

 

[mr moe]

Thanks man your the best.