Problem with the extracted data

**ravik453** · 07-08-2015, 12:09 AM

Hi,

I'm facing a problem in the data i extracted from a tool, i.e as below. At the end it should be 2015 not 2010. It looks correct in the tool but after extracting it is becomes as below. need your help. i'm using Excel 2013.

2488153106302010
2736223406302010
8392047406082010
4522092706062010
4522092705062010
2488153106302010
2736223406302010
8392047406082010
4522092706062010
4522092705062010

Regards,
Ravi.

**FDibbins** · 07-08-2015, 12:16 AM

Hi, welcome to the forum

Excel only allows 15-character values, so it is actually dropping the 5 and replacing it with a 0

Where is the data coming from, and how are you pulling it in? Can you pull it in as text, instead of numeric?

**AlKey** · 07-08-2015, 12:20 AM

Maybe this will help

in B1 and copy dowm

=REPLACE(A1,LEN(A1),1,5)

Row\Col	A	B
1	2488153106302010	2488153106302015
2	2736223406302010	2736223406302015
3	8392047406082010	8392047406082015
4	4522092706062010	4522092706062015
5	4522092705062010	4522092705062015
6	2488153106302010	2488153106302015
7	2736223406302010	2736223406302015
8	8392047406082010	8392047406082015
9	4522092706062010	4522092706062015
10	4522092705062010	4522092705062015

**ravik453** · 07-08-2015, 12:27 AM

Hi Alkey,
Thanks for the solution. But i've one question, the data is not in order, if i've apply this, i need to manually find it & then apply it. Please have a look at this workbook, Column B. https://app.box.com/s/s52i83k1k8bxo5c98fv8ny8nffj02vgi

**ravik453** · 07-08-2015, 12:28 AM

Hi FDibbins,

Thanks, i'm pulling it from a legacy tool called Swan. No, i cannot pull it as text in this tool. Only CSV format. This is the link for the file: https://app.box.com/s/s52i83k1k8bxo5c98fv8ny8nffj02vgi

Regards,
Ravi.

**AlKey** · 07-08-2015, 08:03 AM

Hi Ravi,

Please see attached file with formula in column 'M'. This formula will check the last 4 characters and if it is 2010 it will replace the last character with '5' and if not it will return the the original cell content.

=IF(RIGHT(B2,4)="2010",REPLACE(B2,LEN(B2),1,5),LEFT(B2,LEN(B2)))