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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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.
 
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.
 
RagDye,

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

Barb Reinhardt
 
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.
 
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>
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

find and replace numeric strings in larger text strings 8
Replace Font within Cell 1
Using wild card characters in Find & Replace 6
Find/Replace MSWord wildcard characters from within Excel 6
Replace nonprinting character 2
Using wildcard characters in find & replace 3
Replacing special characters in Excel 2007 3
How can you replace specific characters in a string ? 2

Back
Top