Insert hyphens in text string

Good afternoon.  I have been doing alot of reading on this, but, I am having problems with the examples I have tried to use.  My dilemma is this..I have a field entitled [Customer ID] in TblAccounts.  There are 12 characters in each field, and I need to insert two hyphens, one after the third character, and one after the characters from the right.  In other words, I need this:
 009000000010
to look like this :009-0000000-10

Thank you for your time,
Nikki
Nikki28838Asked:
Who is Participating?
 
jerryb30Connect With a Mentor Commented:
select left(customerID, 3) & "-" and mid(customerid, 4,7) & "-" & right(customerid,2) as newCustomerID from tblAccounts
If it is always the two right characters which need a hyphen before them.
0
 
FlysterCommented:
You could try the format function:

Format([Customer ID],"000-0000000-00")

Flyster
0
 
Nikki28838Author Commented:
Wonderful, thank you very much!
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
jerryb30Commented:
I was going to comment that Flyster's solution was more elegant and you ought to ignore my post.
Please consider re-opening for changing score unless there is some reason you cannot make Flyster's solution work.
0
 
FlysterCommented:
jerryb30,

Thanks for the nice comment. I don't think my work has ever been described as "elegant" before. I have no problem with the points assignment. One nice thing about EE...... there's always another post to respond to! Thanks again.
0
 
Nikki28838Author Commented:
Hello.  Flysters did work...I just happened to use the other method first because I was in a hurry.  I apologize and will gladly move the points...if someone could tell me how?  Again, so sorry.
0
 
jerryb30Commented:
You can, if desired, post a request in community support (with a link to this question), asking that it be re-opened for scoring.
Two ways of doing the same thing, but if I had seen Flyster's response before I posted miine (we were within seconds of each other), I would not have posted.  No need to apologize.  I just wanted to be fair to Flyster.
 
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.