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?
 
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
 
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
 
AndreamaryAuthor Commented:
Terrific...both worked perfectly! Thanks very much...
Andrea
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.