Removing spaces and all text to the right

[captainquigers]

How would I go about removing unwanted spaces from a text string and all text to the right of this?

e.g.

"xx1 pl5605" -----> xx1
"g8 jj77" -----> g8

I've tried various combinations of trim and clean but can't get them to work.

Please note that there can be any number of spaces but I want to remove everything to the right of the first space that occurs.

Cheers,

Paul.

 

[alvechurchdata]

Use the InStr() function to find the location of the spaces in the first string then use Left() to get all the characters before that position.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[captainquigers]

Cheers mate.

That works a treat.

 

[pkailas]

Or you could use the split function.

arString = split(string, " ")


then your first "segment" would be

strVar = arString(0)



_______
I love small animals, especially with a good brown gravy....

 

[alvechurchdata]

Or you could use the split function

Much better idea - I'd forgotten Split. The problem with InStr is that you always have to add 1 or subtract 1 from the number it returns and I always get it wrong first time.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[captainquigers]

I was using the function builder in Access and unfortunately don't have the option of using the split function.

The InStr and Left commands worked fine though.

I'll have to start looking at using VB to manipulate data. It looks far more flexible.

Thanks for the answers,

Cheers.