How copy everything from the first space

SteveL13
SteveL13 used Ask the Experts™
on
If I have this text in a field:

"1234 West Springfield Avenue"

How can I copy everything from the first space to another field?

In other words, the copied text would be "West Springfield Avenue"
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Bill PrewTest your restores, not your backups...
Top Expert 2016

Commented:
If you mean in a query then a calculation like below will extract the part you wanted.  Or were you looking for VBA code?

Right(Field1,Len(Field1)-InStr(Field1," "))

Open in new window


»bp

Author

Commented:
VBA Code.  Sorry.  I should have been more clear.
Test your restores, not your backups...
Top Expert 2016
Commented:
Same code will work in VBA where Field1 and Field2 are variables, like:

Field2 = Right(Field1, Len(Field1) - InStr(Field1, " "))

Open in new window


»bp
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
No Points wanted,
To get just the number you could use:
StreetNumber: Left([CustAddress],InStr([CustAddress]," "))


Not sure what you meant about "Copy"

But using these examples you could build a query like this to display the original field and the two "derived" fields, ...then you could "Use" the derived field anywhere you liked, without having to "copy" anything.
SELECT CustID,CustName, CustAddress, Left([CustAddress],InStr([CustAddress]," ")-1) AS CustStreetNumber, Right([CustAddress],Len([CustAddress])-InStr([CustAddress]," ")) AS CustStreetName
FROM tblCustomers;

derived fields
But again, ...no points wanted as Bill has answered your Q as posted, ...
I was just passing by...
;-)

Jeff

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial