Extract Data To Be Only 4 Digits Number

[muhammad susanto]

hi guys...

i'm looking for some excel formula to extract data number to be 4 digits (general format)
please see this below :

Excel 2007

	E	F
3	data	expected result (general format)
4	1995-11-1608	1995
5	1996-3-197	1996
6	04/04/1993	1993
7	11/06/2008	2008
8	17/03/2011	2011
9	1998-12-1149	1998

<colgroup><col style="width: 25pxpx"><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

any helps, much appreciated..

m.susanto

 

[Joe4]

Are all the entries in Text format, or are some Date format?
In your third example down, how to you get "1996" from "04/04/1993"?

 

[muhammad susanto]

hi Joe4, for data entries, some in text format and some other in date format....

 

[Joe4]

For an entry in cell A2, if they are all text (and assuming that one issue was just a typo), this formula should work:

=IF(OR(ISNUMBER(FIND("-",LEFT(A2,4))),ISNUMBER(FIND("/",LEFT(A2,4)))),RIGHT(A2,4),LEFT(A2,4))

 

[Joe4]

ADVERTISEMENT

If some are dates, try this:

=IF(ISNUMBER(A2),YEAR(A2),IF(OR(ISNUMBER(FIND("-",LEFT(A2,4))),ISNUMBER(FIND("/",LEFT(A2,4)))),RIGHT(A2,4),LEFT(A2,4)))

 

[muhammad susanto]

For an entry in cell A2, if they are all text (and assuming that one issue was just a typo), this formula should work:

=IF(OR(ISNUMBER(FIND("-",LEFT(A2,4))),ISNUMBER(FIND("/",LEFT(A2,4)))),RIGHT(A2,4),LEFT(A2,4))

hi Joe4 not fullywork,
for 04/04/1993, 11/06/2008, 17/03/2011 are still not true. (they are in date format)

 

[muhammad susanto]

If some are dates, try this:

=IF(ISNUMBER(A2),YEAR(A2),IF(OR(ISNUMBER(FIND("-",LEFT(A2,4))),ISNUMBER(FIND("/",LEFT(A2,4)))),RIGHT(A2,4),LEFT(A2,4)))

yeah....working perfecto!!Very quickly answer..
many thanks..