Removing beginning characters in a string

[birtcher]

I am trying to import a text file into Access database. I have over 15,000 part numbers that I am trying to import. These part numbers have a space in the middle. The number of characters before this blank space varies from part number. I am needing some help to remove the beginning of the part number up to the space. Here are some examples and results that I am trying to accomplish.

part # results
3M 7177 7177
3MIND 37081 3081
BD DW8715 DW8715
HUYETT SPC-156-2000 SPC-156-2000

Can someone please help me out. If there was the same amount of character before the blank space, then I would not have a problem. But since there are over 15,000 part numbers to import and all with different amount of characters before the blank space, I am having trouble.Thanks

 

[johnwm]

Hi - welcome to Tek-tips. faq222-2244 will give you guidance on forum usage, especially on basic research. To get you started, look up the INSTR() function and the RIGHT() function which should solve this for you

___________________________________________________________
If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller
Steam Engine Prints

 

[jzr]

Shouldn't be too bad ..
read in the whole line
use 'len' to find the length of the entire line
use 'instr' to find the position of the space
subtract the position of the space from the total
use 'right' to read the rest of the line after the space

IF .. there are multiple spaces in the first part of the string, it might be helpful to use InstrRev instead of instr - to just start from the other end ...

 

[DerFarm]

I have a set of string functions which would solve the problem outright, but they are idiosyncratic to me. If you'd like them, message me and I'll send the app and instructions on how to use them.

 

[HarleyQuinn]

Or you could use the Split() function.

e.g.

strResult = Split(yourpart)(1)

Hope this helps

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[strongm]

>If you'd like them, message me and I'll send the app and instructions on how to use them

We prefer to keep the solutions on this forum where possible.