Opposite of "right([data],7)" and i dont mean Left

[Donny]

I have a data field that contains alpha numeric serial numbers hyphenated like "alphanumeric - numeric". The alphanumeric side is inconsistent in characters, the numeric side is allways 7 characters. I can extract the 7 characters no worries using "right([data],7)". I need to extract the pre hyphenated alphanumeric data without double dipping on the numeric data, is this possible??

Example of the Data

W3/4/5-2697775
W6/7-2706743
W6/7-2709181
W3 -2723739

 

[MichaelRed]

MyData = "W3/4/5-2697775"
? Left(MyData, Instr(MyData, "-&quot)
W3/4/5-


So, WHY NOT ye olde left?

MichaelRed
mred@att.net

There is never time to do it right but there is always time to do it over

 

[jebry]

Hi!

Try using this function: Left([data],InStr([data],"-&quot-1). This will return everything to the left of the hyphen. A couple of things to note, this will not work if the alphanumeric part might contain a hyphen, also, I noticed that your last piece of data in your post had a space before the hyphen. I don't know if that was a typo or not, but I wanted to ask if it is possible for the data to have spaces that it shouldn't? If it can, I can send you a function to remove unneeded spaces.

hth
Jeff Bridgham

 

[Chance1234]

right if its always seven from the right then

use the len function to get the total length

- 7 for the ones you take from the right and what is left over you can use for the left.

I dont know how you are doing this whether in query or code but in code it would be something like


Dim LenStr as Integer

LenStr = (len([data])) -7

Left([data],lenstr)
Right([data],7)

 

[Donny]

Thanx guys,

worked perfectly, the spaces wern't a problem


Donny

 

[Aivars]

Part of strin from pos 1 to pos before symbol -

trim(left([data],instr(1,[data],"-",vbTextCompare)-1))

Example
dim MyDString as String
MyDString="W3/4/5-2697775"
debug.print trim(left(MyDString,instr(1,MyDString,"-",vbTextCompare)-1))
W3/4/5

Aivars