Forum Discussion
Find and Replace Partial numbers
I realize this is a simple problem, but after spending a ridiculous amount of time reading and researching, I can't figure it out!
I have a very long list of birthdates that, when uploaded, were changed from 19** to 20**. I want to correct the mistake by using find and replace, but when I do that any other instance of 20 is also changed to 19. So... instead of 3/20/2067 becoming 3/20/1967, it becomes 3/19/1967.
How can I target and change the year prefix only?
Thanks!
8 Replies
arise2read your approach seems limited unless you are sure none of the birthdays are in the 2000's. I would instead suggest making a new column with a formula to convert it. For example maybe you are sure none are <5 years old (i.e. no birthdays after 2015):
=if(year(a1)>2015, date(2019,month(a1),day(a1)),a1)
that will get you an Excel DATE in whatever format your excel is set to display or if you specifically want the output as text
=if(year(a1)>2015, "2019" & month(a1) & day(a1),a1)
If you don't want that extra column, then once you are done you can copy the column of correct data and 'paste special' -> 'values only' and then delete the formula and incorrect data columns.
mtarler This is excellent, as none of our volunteers will be under the age 16, but there are a few that will have been born after 2000.
However, I am embarrassingly CLUELESS when it comes to formulas. Can you give me specific directions on how to complete this? Should I copy and paste the existing birthdate column to a new column, and put the formula =if(year(a1)>2015, date(2019,month(a1),day(a1)),a1) in the row above that? Do I copy this formula exactly?
Thank you for your help!
arise2read the formula was 'generic' assuming the dates of interest are in column A and starting in row 1 and that you would past the formula and fill down accordingly.
Quick lesson in formulas:
=if(year(a1)>2015, date(2019,month(a1),day(a1)),a1)
the "a1" listed refers to cell (A1).
so if you have all the birthdays in column A starting in A1 you could paste this in B1 and then as you copy or fill down, excel with change the "A1" in the formula to A2, A3, A4, ... so that the cell it is pointing at is always in the same relative location (i.e. the cell to its left)
If you don't want excel to automatically change that pointer then add a "$" in front of what you don't want to change.
for example if instead of "2015" you want to use what ever year you type into cell F1 then use $F$1 so as you fill down excel won't change it and all the cell formulas down the column will still point to $F$1 (note: because you are only filling down in 1 column the relative column isn't changing so you could use F$1 since the F wouldn't change any how)
that said, if your birthdays start in D2 (i.e. D1 is a title) then change all the "a1"s to "D2"s and paste it into cell E2 and fill down.
These are some very important basics you should play with and learn and maybe watch a youtube on if needed because they will help you a lot.
Hi,
Simple with a formula, but not so simple using Find & Replace! I can do it with 3 consecutive Find & Replaces, though no less than that:
Find what: 20/20
Replace with: #20/20
Find what: /20
Replace with: /19
Find what: #20
Replace with: 20
If that's not to your liking, perhaps a formula-based approach is an option for you?
Cheers
To be more safe, it's better 20/19 to replace on #20/19 as well
- Can you give an example for which your suggestion would make a difference?
Regards
