Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find and Replace only the first character
I am trying to replace only the first number in a date.
example: 6/6/10 with 7/6/10 I am doing a find 6/ and a replace 7/ but it changes the 7/6 to 7/7 and I only want to look at the first number in the date. Can anyone help me???? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find and Replace only the first character
Use the 4th arguement to state that you only want to change the first
instance. =SUBSTITUTE(A2,"6","7",1) This premise does assume that you have dates stored as text. If you have them stored as numbers (preferred), you can do: =DATE(YEAR(A2),7,DAY(A2)) -- Best Regards, Luke M "Terry Willard" <Terry wrote in message ... I am trying to replace only the first number in a date. example: 6/6/10 with 7/6/10 I am doing a find 6/ and a replace 7/ but it changes the 7/6 to 7/7 and I only want to look at the first number in the date. Can anyone help me???? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find and Replace only the first character
Assuming that your date format is Month, Day, Year you could use the
following formula to compute the change from June to July. Other months would not be affected. First, assuming your original date is in cell A2, put this formula in Cell B2: =IF(MONTH(A2)=6,DATE(YEAR(A2),7,DAY(A2)),A2) Next, Paste-Special-Values from Cell B2 to Cell A2. Then you can clear the contents of Cell B2. Tom |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Find and Replace only the first character
Thank you so much! This was VERY helpful!!!!!
"tompl" wrote: Assuming that your date format is Month, Day, Year you could use the following formula to compute the change from June to July. Other months would not be affected. First, assuming your original date is in cell A2, put this formula in Cell B2: =IF(MONTH(A2)=6,DATE(YEAR(A2),7,DAY(A2)),A2) Next, Paste-Special-Values from Cell B2 to Cell A2. Then you can clear the contents of Cell B2. Tom |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
more character 160 leading/trailing find/replace problems | Excel Discussion (Misc queries) | |||
find replace the 1st character in a cell | Excel Discussion (Misc queries) | |||
find a replace character question | Excel Discussion (Misc queries) | |||
find a character and replace with a command in Excel | Excel Discussion (Misc queries) | |||
How do you find and replace a Wildcard character in Excel? | Excel Discussion (Misc queries) |