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.

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.

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.

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.

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.

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.

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.

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.

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.