# Extract Data To Be Only 4 Digits Number

##### Well-known Member
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
EF
3dataexpected result (general format)
41995-11-16081995
51996-3-1971996
604/04/19931993
711/06/20082008
817/03/20112011
91998-12-11491998

</tbody>
Sheet1

any helps, much appreciated..

m.susanto

Last edited:

### Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

#### Joe4

##### MrExcel MVP, Junior Admin
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"?

##### Well-known Member
hi Joe4, for data entries, some in text format and some other in date format....

#### Joe4

##### MrExcel MVP, Junior Admin
For an entry in cell A2, if they are all text (and assuming that one issue was just a typo), this formula should work:
Code:
``=IF(OR(ISNUMBER(FIND("-",LEFT(A2,4))),ISNUMBER(FIND("/",LEFT(A2,4)))),RIGHT(A2,4),LEFT(A2,4))``

#### Joe4

##### MrExcel MVP, Junior Admin
If some are dates, try this:
Code:
``=IF(ISNUMBER(A2),YEAR(A2),IF(OR(ISNUMBER(FIND("-",LEFT(A2,4))),ISNUMBER(FIND("/",LEFT(A2,4)))),RIGHT(A2,4),LEFT(A2,4)))``

##### Well-known Member
For an entry in cell A2, if they are all text (and assuming that one issue was just a typo), this formula should work:
Code:
``=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)

Last edited:

##### Well-known Member
If some are dates, try this:
Code:
``=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..

Replies
7
Views
301
Replies
2
Views
287
Replies
3
Views
403
Replies
7
Views
414
Replies
13
Views
1K

1,195,712
Messages
6,011,264
Members
441,598
Latest member
chrispaulpearce

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back