CODEWITHSUNDEEP
excel
Buras
Buras

Reputation: 3099

How do you parse a substring from an Excel cell?

I have a column with the following values:

month
201201     // means  January of 2012
201102     // means February of 2011
201203
201304
...
201307
201106

I need to create a new column that would be convert the last two numbers in the month column into the name of the month, so that 201201 will become Jan-2012, 201106 should become Jun-2011.

How can I parse the last two characters?

Upvotes: 0

Views: 81

Answers (3)

Derek
Derek

Reputation: 8763

This gives you the formatting you want:

=TEXT(DATE(LEFT(A1,4),RIGHT(A1,2),1),"MMM-YYYY")

Upvotes: 2

Jerry
Jerry

Reputation: 71568

You can use LEFT and RIGHT functions and concatenate the results. Assuming the first value is in A2:

=(1&"-"&RIGHT(A2,2)&"-"&LEFT(A2,4))*1

And format the cell as mmm-yyyy.

This assumes that your regional settings have dates as dd/mm/yyyy format.

Otherwise, you'll have to switch the month and date around.

=(RIGHT(A2,2)&"-"&1&"-"&LEFT(A2,4))*1

Upvotes: 1

Carl Manaster
Carl Manaster

Reputation: 40356

=DATE(MID(A2,1,4),MID(A2,5,2),1)

Upvotes: 1

Related Questions