CODEWITHSUNDEEP
exceldigitsvba
nbwest76
nbwest76

Reputation: 59

Excel Macro to Replace last four digits if containing specific number

I have a spreadsheet with 2000 rows. My concern is with column B which contains 17 digit numbers. I'd like to have a macro check all numbers in column B and if the last four digits = "0000" I'd like to change the last four digits to "0001", also if the last four digits = "1900" I'd like to change the last four digits to "1998". Any other scenario can be skipped.

Thanks

Upvotes: 0

Views: 1045

Answers (3)

Dan Donoghue
Dan Donoghue

Reputation: 6216

Another alternative:

=IF(RIGHT(B1,4)="0000",B1+1,IF(RIGHT(B1,4)="1900",B1+98,B1))

Upvotes: 0

0xMB
0xMB

Reputation: 881

You can use this:

=LEFT(B1,13)&IF(RIGHT(B1,4)="0000","0001",IF(RIGHT(B1,4)="1900","1998",RIGHT(B1,4)))

Just place this in another column and afterwards replace column B with the values from this column.

Upvotes: 1

Paresh J
Paresh J

Reputation: 2419

You can use formula for the same. Following is the excel formula for the same:

=IF(RIGHT(B2,4)="0000",CONCATENATE(MID(B2,1,LEN(B2)-4),"0001"),IF(RIGHT(B2,4)="1900",CONCATENATE(MID(B2,1,LEN(B2)-4),"1998"),B2))

Upvotes: 0

Related Questions