How to parse space delimited string.

John Kincaid
John Kincaid used Ask the Experts™
on
I need to capture all text to the right of the last space and everything (including spaces) to the left of the last space.  I have been all over the Mid, Right and Left functions. All messed up here!!!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
John TsioumprisSoftware & Systems Engineer

Commented:
The best solution is the Split Command
Dim AllText() as String
AllText = Split(YourText," ")
For i = Lbound(AllText) To UBound(AllText)
Debug.Print AllText(i)
Next

Open in new window

This will convert your text to an array splitted on each space
e.g.
YourText = "A Simple Test"
AllText(0) = "A"
AllText(1) = "Simple"
AllText(2) = "Test
This will give you the everything to the right of the last space:

 Mid(YourString, Len(YourString))

Open in new window


and everything to the left of the last space
Left(YourString, InStrRev(YourString, ""))

Open in new window

Bill PrewIT / Software Engineering Consultant
Top Expert 2016
Commented:
If you want to do it in a SQL query then you couls use:

Mid([Text1],InStrRev([Text1]," ")+1) AS RightPart
Mid([Text1],1,InStrRev([Text1]," ")-1) AS LeftPart



»bp
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Anders

MyString = "Aaron Guzman"
 Mid(YourString, Len(YourString)) returned "n"
and
Left(YourString, InStrRev(YourString, "")) returned "Aaron Guzman"
Top Expert 2014

Commented:
In addition to Bill's correct solution, you can also use the Left() function:
Left([Text1],InStrRev([Text1]," ")-1) AS LeftPart

Open in new window

Author

Commented:
Anders, forgive me I am wrong, your code worked fine.
Bill, yours did as well.

Thanks to both of you!

Author

Commented:
Thanks again to everyone!
Bill PrewIT / Software Engineering Consultant
Top Expert 2016

Commented:
Welcome.


»bp
Your welcome :)

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