Extract Data to LEFT of Character

[rjonas]

I thought I had this figured out but I'm stumped...

I have a customer number list that looks like this:

1234_Cust
12345_Cust

I want to strip off the "_Cust" part and be left with:

1234
12345

I thought I could use something like this:

Left([CustNO],Len([CustNO])-InStr([CustNO],"_"))

However, this gives me results of:

1234
1234

Feels like the formula above is close but I'm missing some component.. :banghead:

Thoughts? Suggestions??

Any help is appreciated!

Thanks, Ron

 

[RuralGuy]

Try this:

Dim Junk As String
Junk = Trim(CustNo)
MsgBox "[" & Left([Junk],InStr([Junk],"_")-1) & "]"

 

[rjonas]

Thanks RG... I will try this... Now I need to figure out the syntax to do this in a query (i'm not very good at VBA coding...)

 

[Guus2005]

Val("1234_Cust") returns 1234

or

Split("1234_Cust","_")(0) returns 1234

HTH

 

[RuralGuy]

Left([CustNo],InStr([CustNo],"_")-1)
...will probably work.

 

[rjonas]

RG - indeed it did... you're the best! Thanks!!

 

[RuralGuy]

Glad we could help.