Remove characters before 1st space

[davea300]

What forumla could I use in a report field to remove all characters before the first space in a text field e.g.

32A Bank Street --> Bank Street
19 Alder Street --> Alder Street


I've tried =Right([JLADD1],Len([JLADD1])-2) but this obviously doesn't work for the first example above.

 

[pr2-eugin]

There are several String Manipulation Functions in VBA, that you can look at..

In this special case you can use Mid and InStr

[URL="http://www.techonthenet.com/access/functions/string/mid.php"]Mid([/URL][JLADD1], [URL="http://www.techonthenet.com/access/functions/string/instr.php"]InStr([/URL][JLADD1]," ")+1)

That should help you..

 

[Brianwarnock]

Although the poster said characters before the space, I'm guessing he also wants to remove the space and therefore needs to add 1 to the position returned by the Instr

Brian

 

[pr2-eugin]

I think the snow has actually made my brain freeze.. I have been not paying attention in some of my answers today..

 

[Brianwarnock]

You answered the question as stated and I maybe wrong, so don't beat yourself up.

Brian

 

[davea300]

This is true i do need to remove the space as well! Thanks for the replies!