Mr Excel & excelisfun Trick 57: Extract Many Years from Just Two Years

See Mr Excel and excelisfun take the text string "1997-2000" and create a row of years consisting of 1997, 1998, 1999, 2000. See the functions LEFT, RIGHT, COLUMNS and IF functions. VBA or Formula. Learn about the difference between numbers and text for Logical Formulas.

Category:

Science & Technology

Tags:

• Excel
• 2007
• 2003
• excelisfun
• Bill
• Mr
• Jelen
• Mike
• Gel
• Girvin
• Highline
• Community
• College
• text
• string
• create
• row
• of
• years
• consisting
• 1997
• 1998
• 1999
• 2000
• functions
• LEFT
• RIGHT
• COLUMNS
• Totally
• cool
• formula
• Learn
• about
• the
• difference
• between
• numbers
• and
• for
• Logical
• Formulas
• VBA
• Loop

[loyaus]

Thanks for giving simple way to extract dates.

[ExcelIsFun]

You are welcome!

[rajanverma100]

I like VBA Macro,No much calculation of Formulas no more data added into Excel file.. simply just run the macro and complete My Work..

In Function My Answer is this

=IF(COLUMNS($B$1:B1)<=(RIGHT($­A1,4)-LEFT($A1,4)+1),LEFT($A1,­4)+COLUMNS($B$1:B1)-1,"")

[ExcelIsFun]

Cool!

[rajanverma100]

I like VBA Macro,No much calculation of Formulas no more data added into Excel file.. simply just run the macro and complete My Work..

[ExcelIsFun]

Yes, VBA is a great way to go!

[ExcelIsFun]

Some do love the VBA, though!

[ExcelIsFun]

I am like you, though, I always want to solve it myself first. After hanging out at the Mr Excel Message Board for years though, I understand that I almost never get the best most efficient solution, so after i try, I look at other people's cool solutions!

[ExcelIsFun]

I don't think it needs to be that complcated. Watch the video and see if you agree. It basically comes down to LEFT, RIGHT and COLUMNS...