I need a query that formats a set of numbers.

I just need an Access Query that changes the format of a set of numbers like this

08-01-   6-0059-0000-0000

into this

0801-0006-0059-0000-0000

Thank you
GravitaZ24Asked:
Who is Participating?
 
FlysterConnect With a Mentor Commented:
See if this works for you:

SELECT Sheet1.[Unique], Replace([Unique],"-","") AS Step1, Replace([Step1]," ",0) AS Step2, Format([Step2],"0000-0000-0000-0000-0000-000") AS Step3
FROM Sheet1;
0
 
FlysterCommented:
If your first set of numbers is always in that format, then this should work for you:

NewFormat: Left([YourNumberField],2) & Mid([YourNumberField],4,2) & "-000" & Mid([YourNumberField],8,1) & "-" & Right([YourNumberField],14)

Flyster
0
 
GravitaZ24Author Commented:
I'm sorry that worked for the first few numbers, but there was more to it than I realized, I attached a sample of the numbers, I have to get them into this format xxxx-xxxx-xxxx-xxxx-xxxx-xxx. The second groups of numbers is where i'm having trouble, they have to be filled in with zeros where there are blanks.
0
 
GravitaZ24Author Commented:
I forgot to attach to the last post, here it is
Sample.xls
0
 
GravitaZ24Author Commented:
Thank you! that worked!
0
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.