Home |
Search |
Today's Posts |
#1
|
|||
|
|||
How can I replace 2 of 5 characters within an cell in MS Excel?
I am using MS Office 2003 and attempting to use a wildcard to find and
replace characters within cells within a column (ex. 161000 to be changed to 201000). In a column of such numeric values the first two digits are consistent while the last 4 digits are not. Using the wildcard feature in the find and replace menu, I highlighted the column, clicked find and replace, "Find what = 16*, Replace with = 20*". This changed 161000 to 20*, when I wanted 201000. Any assistance would be greatly appreciated. |
#2
|
|||
|
|||
I'd probably do this with an equation and create another column of data.
Let's say your data is in A1. =IF(LEFT(A1,2)="16",VALUE(20&MID(A1,3,4)),A1) I'm assuming that the length of your cells is 6 characters long. If it isn't, you'll need to adjust the MID() function. "pmhall" wrote: I am using MS Office 2003 and attempting to use a wildcard to find and replace characters within cells within a column (ex. 161000 to be changed to 201000). In a column of such numeric values the first two digits are consistent while the last 4 digits are not. Using the wildcard feature in the find and replace menu, I highlighted the column, clicked find and replace, "Find what = 16*, Replace with = 20*". This changed 161000 to 20*, when I wanted 201000. Any assistance would be greatly appreciated. |
#3
|
|||
|
|||
If all the data is 6 digits, then
=A1-160000+200000 -- Gary's Student "Barb R." wrote: I'd probably do this with an equation and create another column of data. Let's say your data is in A1. =IF(LEFT(A1,2)="16",VALUE(20&MID(A1,3,4)),A1) I'm assuming that the length of your cells is 6 characters long. If it isn't, you'll need to adjust the MID() function. "pmhall" wrote: I am using MS Office 2003 and attempting to use a wildcard to find and replace characters within cells within a column (ex. 161000 to be changed to 201000). In a column of such numeric values the first two digits are consistent while the last 4 digits are not. Using the wildcard feature in the find and replace menu, I highlighted the column, clicked find and replace, "Find what = 16*, Replace with = 20*". This changed 161000 to 20*, when I wanted 201000. Any assistance would be greatly appreciated. |
#4
|
|||
|
|||
That will work if the first two characters are either 16 or 20. If it's 01,
02, 03, ... 17, 18, 19 it won't give what pmhall wants. "Gary's Student" wrote: If all the data is 6 digits, then =A1-160000+200000 -- Gary's Student "Barb R." wrote: I'd probably do this with an equation and create another column of data. Let's say your data is in A1. =IF(LEFT(A1,2)="16",VALUE(20&MID(A1,3,4)),A1) I'm assuming that the length of your cells is 6 characters long. If it isn't, you'll need to adjust the MID() function. "pmhall" wrote: I am using MS Office 2003 and attempting to use a wildcard to find and replace characters within cells within a column (ex. 161000 to be changed to 201000). In a column of such numeric values the first two digits are consistent while the last 4 digits are not. Using the wildcard feature in the find and replace menu, I highlighted the column, clicked find and replace, "Find what = 16*, Replace with = 20*". This changed 161000 to 20*, when I wanted 201000. Any assistance would be greatly appreciated. |
#5
|
|||
|
|||
You're over thinking!<g
Find What = 16 Replace With = 20 -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "pmhall" wrote in message ... I am using MS Office 2003 and attempting to use a wildcard to find and replace characters within cells within a column (ex. 161000 to be changed to 201000). In a column of such numeric values the first two digits are consistent while the last 4 digits are not. Using the wildcard feature in the find and replace menu, I highlighted the column, clicked find and replace, "Find what = 16*, Replace with = 20*". This changed 161000 to 20*, when I wanted 201000. Any assistance would be greatly appreciated. |
#6
|
|||
|
|||
RagDye,
What is the cell is 120160? I don't think the global change you are suggesting will work in this sense. Barb Reinhardt "RagDyeR" wrote: You're over thinking!<g Find What = 16 Replace With = 20 -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "pmhall" wrote in message ... I am using MS Office 2003 and attempting to use a wildcard to find and replace characters within cells within a column (ex. 161000 to be changed to 201000). In a column of such numeric values the first two digits are consistent while the last 4 digits are not. Using the wildcard feature in the find and replace menu, I highlighted the column, clicked find and replace, "Find what = 16*, Replace with = 20*". This changed 161000 to 20*, when I wanted 201000. Any assistance would be greatly appreciated. |
#7
|
|||
|
|||
find / replace will really butcher 161616
-- Gary's Student "pmhall" wrote: I am using MS Office 2003 and attempting to use a wildcard to find and replace characters within cells within a column (ex. 161000 to be changed to 201000). In a column of such numeric values the first two digits are consistent while the last 4 digits are not. Using the wildcard feature in the find and replace menu, I highlighted the column, clicked find and replace, "Find what = 16*, Replace with = 20*". This changed 161000 to 20*, when I wanted 201000. Any assistance would be greatly appreciated. |
#8
|
|||
|
|||
If it isn't 6 characters, you can simply take all the characters on the right
with =IF(LEFT(A1,2)="16",VALUE(20&MID(A1,3,LEN(A1)-2)),A1) or =IF(LEFT(A1,2)="16",VALUE(20&MID(A1,3,255)),A1) since specifying a number that's too big as the 3rd argument doesn't cause an error. On Wed, 8 Jun 2005 06:55:03 -0700, "Barb R." wrote: I'd probably do this with an equation and create another column of data. Let's say your data is in A1. =IF(LEFT(A1,2)="16",VALUE(20&MID(A1,3,4)),A1) I'm assuming that the length of your cells is 6 characters long. If it isn't, you'll need to adjust the MID() function. "pmhall" wrote: I am using MS Office 2003 and attempting to use a wildcard to find and replace characters within cells within a column (ex. 161000 to be changed to 201000). In a column of such numeric values the first two digits are consistent while the last 4 digits are not. Using the wildcard feature in the find and replace menu, I highlighted the column, clicked find and replace, "Find what = 16*, Replace with = 20*". This changed 161000 to 20*, when I wanted 201000. Any assistance would be greatly appreciated. |
#9
|
|||
|
|||
You're right folks - I mis-read the OP.
This has been happening all too often lately, so I guess I'll try to get away with it by saying that my Senior Moments are catching up with me.<bg -- Regards, RD -------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit! ------------------------------------------------------------------- "Gary's Student" wrote in message ... find / replace will really butcher 161616 -- Gary's Student "pmhall" wrote: I am using MS Office 2003 and attempting to use a wildcard to find and replace characters within cells within a column (ex. 161000 to be changed to 201000). In a column of such numeric values the first two digits are consistent while the last 4 digits are not. Using the wildcard feature in the find and replace menu, I highlighted the column, clicked find and replace, "Find what = 16*, Replace with = 20*". This changed 161000 to 20*, when I wanted 201000. Any assistance would be greatly appreciated. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Append Text to Cell Values Using Replace | Excel Discussion (Misc queries) | |||
replace cell contents | Excel Worksheet Functions | |||
limit number of characters in a cell | Excel Discussion (Misc queries) | |||
Visible cell characters | Excel Discussion (Misc queries) | |||
#### error if cell has more than 255 characters | Excel Discussion (Misc queries) |