Book1 | ||||
---|---|---|---|---|
A | B | |||
1 | 19 years | 19 Years | ||
2 | 19 years 15 days | 19 Years | ||
3 | 19 years 0 months 15 days | 19 Years | ||
4 | 19 years 2 months | 19 Years | ||
5 | 19 years 3 months 20 days | 19 Years 3 months | ||
6 | 19 years 6 months 6 days | 19 Years 6 months | ||
7 | 19 years 9 months 20 days | 20 Years | ||
8 | 19 years 11 months 20 days | 20 Years | ||
Sheet422 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
B1 | =LEFT(A1,FIND(" ",A1)-1)+IF(ISNUMBER(SEARCH("month",A1)),IF(MID(A1&" 0",SEARCH("years",A1)+6,2)+0>=9,1,0),0)&" Years"&IFERROR(IF(ISNUMBER(SEARCH("month",A1)),SUBSTITUTE(" "&CHOOSE(MID(A1,SEARCH("years",A1)+6,2)+0,,,3,3,3,6,6,6,,,,)&" months"," months",""),""),"") |
IF A1=19 years 7 months 1days, B1=19 years 6, months is not appearing.Hi,
Depending on how something like my Row 2 and Row 3 samples are inputted (i.e. no month, 0 month), the formula may be shortened a bit.
Also, if the number of years can be 1 or 0, I'll modify the formula to suit:
A B 19 years 19 Years 19 years 15 days 19 Years 19 years 0 months 15 days 19 Years 19 years 2 months 19 Years 19 years 3 months 20 days 19 Years 3 months 19 years 6 months 6 days 19 Years 6 months 19 years 9 months 20 days 20 Years 19 years 11 months 20 days 20 Years
<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]8[/TD]
</tbody>Sheet422
[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD="align: left"]=LEFT(A1,FIND(" ",A1)-1)+IF(ISNUMBER(SEARCH("month",A1)),IF(MID(A1&" 0",SEARCH("years",A1)+6,2)+0>=9,1,0),0)&" Years"&IFERROR(IF(ISNUMBER(SEARCH("month",A1)),SUBSTITUTE(" "&CHOOSE(MID(A1&" 1",SEARCH("years",A1)+6,2)+0,,,3,3,3,6,6,6,,,,)&" months"," months",""),""),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Book1 | ||||
---|---|---|---|---|
A | B | |||
1 | 19 years | 19 Years | ||
2 | 19 years 15 days | 19 Years | ||
3 | 19 years 0 months 15 days | 19 Years | ||
4 | 19 years 2 months | 19 Years | ||
5 | 19 years 3 months 20 days | 19 Years 3 months | ||
6 | 19 years 6 months 6 days | 19 Years 6 months | ||
7 | 19 years 7 months 1days | 19 Years 6 months | ||
8 | 19 years 9 months 20 days | 20 Years | ||
9 | 19 years 11 months 20 days | 20 Years | ||
Sheet422 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
B1 | =LEFT(A1,FIND(" ",A1)-1)+IF(ISNUMBER(SEARCH("month",A1)),IF(MID(A1&" 0",SEARCH("years",A1)+6,2)+0>=9,1,0),0)&" Years"&IFERROR(IF(ISNUMBER(SEARCH("month",A1)),SUBSTITUTE(" "&CHOOSE(MID(A1,SEARCH("years",A1)+6,2)+0,,,3,3,3,6,6,6,,,,)&" months"," months",""),""),"") |
months is not appearing in the result.Hi,
Depending on how something like my Row 2 and Row 3 samples are inputted (i.e. no month, 0 month), the formula may be shortened a bit.
Also, if the number of years can be 1 or 0, I'll modify the formula to suit:
A B 19 years 19 Years 19 years 15 days 19 Years 19 years 0 months 15 days 19 Years 19 years 2 months 19 Years 19 years 3 months 20 days 19 Years 3 months 19 years 6 months 6 days 19 Years 6 months 19 years 9 months 20 days 20 Years 19 years 11 months 20 days 20 Years
<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]8[/TD]
</tbody>Sheet422
[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD="align: left"]=LEFT(A1,FIND(" ",A1)-1)+IF(ISNUMBER(SEARCH("month",A1)),IF(MID(A1&" 0",SEARCH("years",A1)+6,2)+0>=9,1,0),0)&" Years"&IFERROR(IF(ISNUMBER(SEARCH("month",A1)),SUBSTITUTE(" "&CHOOSE(MID(A1&" 1",SEARCH("years",A1)+6,2)+0,,,3,3,3,6,6,6,,,,)&" months"," months",""),""),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Hi,
Depending on how something like my Row 2 and Row 3 samples are inputted (i.e. no month, 0 month), the formula may be shortened a bit.
Also, if the number of years can be 1 or 0, I'll modify the formula to suit:
A B 19 years 19 Years 19 years 15 days 19 Years 19 years 0 months 15 days 19 Years 19 years 2 months 19 Years 19 years 3 months 20 days 19 Years 3 months 19 years 6 months 6 days 19 Years 6 months 19 years 9 months 20 days 20 Years 19 years 11 months 20 days 20 Years
<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]8[/TD]
</tbody>Sheet422
[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD="align: left"]=LEFT(A1,FIND(" ",A1)-1)+IF(ISNUMBER(SEARCH("month",A1)),IF(MID(A1&" 0",SEARCH("years",A1)+6,2)+0>=9,1,0),0)&" Years"&IFERROR(IF(ISNUMBER(SEARCH("month",A1)),SUBSTITUTE(" "&CHOOSE(MID(A1&" 1",SEARCH("years",A1)+6,2)+0,,,3,3,3,6,6,6,,,,)&" months"," months",""),""),"")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
Book1 | ||||
---|---|---|---|---|
A | B | |||
1 | 19 years | 19 Years | ||
2 | 19 years 15 days | 19 Years | ||
3 | 19 years 0 months 15 days | 19 Years | ||
4 | 19 years 2 months | 19 Years | ||
5 | 19 years 3 months | 19 Years 6 months | ||
6 | 19 years 3 months 20 days | 19 Years 6 months | ||
7 | 19 years 6 months 6 days | 19 Years 6 months | ||
8 | 19 years 7 months 1days | 19 Years 6 months | ||
9 | 19 years 9 months 20 days | 20 Years | ||
10 | 19 years 11 months 20 days | 20 Years | ||
Sheet422 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
B1 | =LEFT(A1,FIND(" ",A1)-1)+IF(ISNUMBER(SEARCH("month",A1)),IF(MID(A1&" 0",SEARCH("years",A1)+6,2)+0>=9,1,0),0)&" Years"&IFERROR(IF(ISNUMBER(SEARCH("month",A1)),SUBSTITUTE(" "&CHOOSE(MID(A1,SEARCH("years",A1)+6,2)+0,,,6,6,6,6,6,6,,,,)&" months"," months",""),""),"") |