Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.


Split Text Field When Spaces Vary

Split Text Field When Spaces Vary

I have a field where the spacing in the middle could change.

Example 1: 332 31
Example 2: 332 9
Example 3: 041 12

I need to split this field into 2 parts. The space in the middle is what I was trying to split on but my example # 2 is returning a space then the 9. I need it to just return the 9. I also tried the left and the right formulas but in some cases it was grabbing just the 9 but then it would throw off the others (I would return the 1 in Example 1 and the 2 in Example 2).

I want my results to be:
Truck# Stop#
Example 1: 332 31
Example 2: 332 9
Example 3: 041 12

Any advice would be greatly appreciated. Thanks in advance!

RE: Split Text Field When Spaces Vary

Try using Instr() instead

Right(Yourfield, Instr(yourField, " ")-1)
Mid(Yourfield, Instr(yourField, " ")+1,2)


RE: Split Text Field When Spaces Vary

You could do the following:

Trim(Split({table.field}," ")[2])


RE: Split Text Field When Spaces Vary

Thank you both! I'm all set!

RE: Split Text Field When Spaces Vary

Actually, I think my suggestion would not quite work. In case of double spaces, try this for the second section:

Split(Replace({table.field}," "," ")," ")[2]

It doesn't display properly here, but there are two spaces within the first set of quotes, and one within the other two.


RE: Split Text Field When Spaces Vary

i was actually looking through other threads after i posted this and i came across one and tried it and it works. i have no idea why it works though. i've never used ubound before. i'm using it for the Stop # part of the field split.

Split({oeeh1.route}," ")[Ubound(Split({oeeh1.route}," "))]

RE: Split Text Field When Spaces Vary

Unbound will return the last element in the array created by the split function, so that would work also.


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close