How copy everything from the first space

SteveL13 used Ask the Experts™
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"
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

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



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

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

Open in new window

Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

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, points wanted as Bill has answered your Q as posted, ...
I was just passing by...


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