Parse field in access query

I need to be able to split a field into 3 different sections in my access query.

The first section will always be the first 6 characters in the field.
The second section will always be the last 3 characters in the field
And the third part will be what's in the middle. The middle will consist of either 1, 2, or 3 characters to the right and 1 or two spaces to the left. I need to drop the spaces and then pull the characters that are left which can be either 1, 2, or 3 characters.


358618 18000 = Parsed would be: "358618" "18" "000"
358618  2000 = Parsed would be: "358618: "2" "000"

How can I do this?
Lawrence SalvucciInformation Technology ManagerAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

Dan CraciunConnect With a Mentor IT ConsultantCommented:
MID(field, 1, 6)
FORMAT(MID(field, 7, 3), "0")
MID(field, 10, 3)

Lawrence SalvucciInformation Technology ManagerAuthor Commented:
That's perfect! Thank you very much for your help and QUICK response!
Dan CraciunIT ConsultantCommented:
Glad I could help!
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Dale FyeCommented:
That might work for the example you provided, but it will not work for all cases, since you have a varying number of characters in your string.

Left(FieldName, 6)
Trim(Mid(FieldName, 7, Len(FieldName])-9))
Right(FieldName, 3)
Dan CraciunIT ConsultantCommented:
Dale, I think the middle number is left padded with spaces.
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Yes the middle will always be padded with either 1 or 2 spaces to the left.
Dale FyeCommented:

MID(field, 7, 3)

Will always give you 3 characters

and in the 2nd example, there are only 11 characters, so

MID(field, 10, 3)

will give you:



Dan CraciunIT ConsultantCommented:
Nope. 12 chars both
358618  2000
358618 18000

Open in new window

Dale FyeCommented:
OK, proportional spacing got me on that one, but the description the OP provided made it sound like there were a variable number of characters in the middle section.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.