How can I replace 2 of 5 characters within an cell in MS Excel?

P

pmhall

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.
 
B

Barb R.

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.
 
B

Barb R.

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.
 
R

RagDyeR

You're over thinking!<g>

Find What = 16

Replace With = 20
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

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.
 
B

Barb R.

RagDye,

What is the cell is 120160? I don't think the global change you are
suggesting will work in this sense.

Barb Reinhardt
 
M

Myrna Larson

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.
 
R

RagDyer

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>
 
Top