• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

A string problem

P

pinoy-exceler

New Member
Hello Excel experts!

How do I convert the string "11-15" to "2011,2012,2013,2014,2015"
The base strings are abbreviated year ranges and I need to convert these strings in a format stated above. Is there a way to do it without resorting to VBA?


Thank you in advance!
 
In one cell? Or in multiple cells?
Are there any years before 2000 like 99-15?
What is the maximum range you are looking at?
 
shrivallabha said:
In one cell? Or in multiple cells?
Are there any years before 2000 like 99-15?
What is the maximum range you are looking at?
Click to expand...

Oh yeah I forgot, the year range starts at 2000 until 2020. Yes in one cell. A1 contains the year range string, B2 should contain the converted string.

You can use helper columns if you want, or if you can cram the whole solution into one formula, the better. Thank you in advance
 
Last edited:
Direct formula solution will be cumbersome for sure to maintain. VBA UDF will be best bet.

Attached is one more way of looking at it.
 

Attachments

  • ConcatYear.xlsx
    9.2 KB · Views: 5
Thank you narayank and shrivallabha! Your solutions work. I know it was a long shot to get a one formula solution for this as Microsoft decided not to make the concatenate function work in an array formula. It's frustrating. Thanks again guys!
 
XOR LX said:
They have now! About time!

https://support.office.com/en-us/article/CONCAT-function-9b1a9a3f-94ff-41af-9736-694cbd6b4ca2

https://support.office.com/en-us/article/TEXTJOIN-function-357b449a-ec91-49d0-80c3-0e8fc845691c

Regards
Click to expand...

About time!

Anyone here using Excel 2016? Would you mind testing the below array formula out? I just need to know if the formula works. Excel Online doesn't allow array formulas to be entered. I have been wrestling with Excel 2010 for hours to find a workaround - but to no avail.

=LEFT(CONCAT(TRANSPOSE(ROW(INDIRECT(LEFT($A$1,2)+2000&":"&RIGHT($A$1,2)+2000))&",")),LEN(CONCAT(TRANSPOSE(ROW(INDIRECT(LEFT($A$1,2)+2000&":"&RIGHT($A$1,2)+2000))&",")))-1)

Thank you in advance.
 
Last edited:
You must log in or register to reply here.
Back
Top