Extract Number From Different Format (Date Vs General)

[muhammad susanto]

hi guys...

how to extract this number which 2 condition (date format and general format)

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]data sample[/TD]
[TD]desired result[/TD]
[/TR]
[TR]
[TD]08/12/2005 (date format)[/TD]
[TD]2005[/TD]
[/TR]
[TR]
[TD]01/07/1992 (date format)[/TD]
[TD]1992[/TD]
[/TR]
[TR]
[TD]2001-10-380 (general format)[/TD]
[TD]2001[/TD]
[/TR]
[TR]
[TD]1986-7-451254 ((general format)[/TD]
[TD]1986[/TD]
[/TR]
</tbody>[/TABLE]

hope, someone could help me...

m.susanto

 

[mole999]

try =(sample column) and format as YYYY

and see what is left over, not sure the best way to extract what is basically =LEFT(sample column,4)

 

[Kenneth Hobson]

If they are dates, as mole999 said:
=TEXT(A1,"yyyy")
or
=YEAR(A1)

If string with year in first 4 characters:
=LEFT(A1,4)

or for either:
=IF(CELL("type", A1)="l",LEFT(A1,4),YEAR(A1))

 

[bosco_yip]

B2, copy down :

=IF(LEFT(CELL("format",A2))="D",YEAR(A2),LEFT(A2,4))

Regards

 

[SpillerBD]

my proposed variation is

=IFERROR(TEXT(A1,"YYYY"),LEFT(A1,4))

 

[muhammad susanto]

hi guys, thank for all, it works

i have a new challenge for you to reverse order :

08/12/2005 (date format)----2005-12-08 (as text format)
01/07/1992 (date format)---1992-07-01 (as text format)
1986-7-45 (as text) ----1986-7-45 (as text- not change)
2001-10-380 (as text)---2001-10-380 (as text- not change)

 

[Rick Rothstein]

hi guys, thank for all, it works

i have a new challenge for you to reverse order :

08/12/2005 (date format)----2005-12-08 (as text format)
01/07/1992 (date format)---1992-07-01 (as text format)
1986-7-45 (as text) ----1986-7-45 (as text- not change)
2001-10-380 (as text)---2001-10-380 (as text- not change)

Give this formula a try...

=IF(COUNTIF(A1,"*-*"),A1,TEXT(A1,"yyyy-mm-dd"))

 

[muhammad susanto]

Give this formula a try...

=IF(COUNTIF(A1,"*-*"),A1,TEXT(A1,"yyyy-mm-dd"))

work perfect like a charm!!!
Ricks, thank a lot....

 

[AlKey]

Or this

=IF(ISNUMBER(A1),YEAR(A1),--LEFT(A1,4))

 

[Rick Rothstein]

work perfect like a charm!!!
Ricks, thank a lot....

You are quite welcome, I'm glad I was able to be of some help. In thinking about it, this formula would probably also work...

=IFERROR(TEXT(A1,"yyyy-mm-dd"),A1)