CODEWITHSUNDEEP
excelworksheet-function
Bob
Bob

Reputation: 885

Rearranging text data in excel

I have a table like this:-

Item            Model
------------------------
A               10022009
B               10032006
C               05081997

I need to rearrange/convert the Model column into this format in excel:-

Item            Model
------------------------
A               20090210
B               20060310
C               19970805

The Model column is character.

Upvotes: 1

Views: 6979

Answers (2)

Alain
Alain

Reputation: 27250

Make a new column with the formula:

=DATE(RIGHT(B1,4),MID(B1,3,2),LEFT(B1,2))

This will convert the string into a date (assuming the format is initial dd/mm/yyyy) Next, format the result of this formula with the custom format yyyymmdd. This will now be a proper date, formatted as year, month, day.

Upvotes: 0

Adriaan Stander
Adriaan Stander

Reputation: 166486

Try something like

=RIGHT(B2,4) & MID(B2,3,2)&LEFT(B2,2)

Have a look at

Upvotes: 3

Related Questions