Modify a column by adding a prefix, changing the year format and reducing the number of digits

We receive weekly reports listing changes. Column A contains ID #'s. For our purposes, we would like to modify the ID number as follows:

Add a two-character alpha prefix: WO
Truncate the year from 4-digits to 2-digits
Reduce the number of digits following the dash from 7 digits to the last 5 digits

Example of original ID: 2015-0000247
Example of modified ID: WO 15-00247

I've attached a sample spreadsheet.

Thanks!
Andrea
Changing-ID-number.xlsx
AndreamaryAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Tj aCommented:
Create a new column with this formula. (assuming the 1st cell to change is in A1):

=CONCATENATE("WO ", RIGHT(A1, 10))

then drag it down or double click the plus sign on the bottom right of the cell.
0
Tj aCommented:
Actually try this one instead. sorry.

=CONCATENATE("WO ", MID(A2,3,3), RIGHT(A2,5))

See the attached file.
Changing-ID-number.xlsx
0
Saurabh Singh TeotiaCommented:
Use this formula and drag it down to see the desired results..

="WO "&MID(A2,3,3)&RIGHT(A2,5)

Saurabh...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AndreamaryAuthor Commented:
Terrific...both worked perfectly! Thanks very much...
Andrea
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.