Formula to change content in a cell

[Guest]

Hi
I have a column of vendor numbers such as m6501p01 in one cell, the next record is m6501p02 and so on. The person that entered the numbers should have entered an S instead of a 5. I have over 100 records to change. Is there a forumla that will change all the 5's in that position to an S

Thanks!

 

[Alan]

Hi,
Try
=SUBSTITUTE(A1,MID(A1,3,1),5) in another range,
Copy > Paste Special > Values back to the original range,
Regards,
Alan.

Hi,
I have a column of vendor numbers such as m6501p01 in one cell, the next

record is m6501p02 and so on. The person that entered the numbers should
have entered an S instead of a 5. I have over 100 records to change. Is
there a forumla that will change all the 5's in that position to an S?

 

[Greg Rivet]

Alan, I believe the formula should be
=SUBSTITUTE(A1,MID(A1,3,1),"S",1)

Greg

 

[Paul B]

Laura, try this, =SUBSTITUTE(A1,LEFT(A1,3),"m6s") put in another column,
copy down and then paste special valves over your data
--
Paul B
Always backup your data before trying something new
Using Excel 97 & 2000
Please post any response to the newsgroups so others can benefit from it
** remove news from my email address to reply by email **

Hi,
I have a column of vendor numbers such as m6501p01 in one cell, the next

record is m6501p02 and so on. The person that entered the numbers should
have entered an S instead of a 5. I have over 100 records to change. Is
there a forumla that will change all the 5's in that position to an S?

 

[Robert]

select all cells to be changed
select from the menu "edit"
select "replace"
type in the replace box "m65"
type in the with what box "m6s"
select "replace all"
done

-----Original Message-----
Hi,
I have a column of vendor numbers such as m6501p01 in one

cell, the next record is m6501p02 and so on. The person
that entered the numbers should have entered an S instead
of a 5. I have over 100 records to change. Is there a
forumla that will change all the 5's in that position to
an S?

 

[Alan]

Greg,
AFAICS your formula returns '1', please enlighten me,
Regards,
Alan.

 

[Alan]

That was my first thought, but in potentially lengthy strings the same three
letters may turn up elsewhere

 

[Alan]

Greg,
You're right, got it back to front, apologies to all,
Regards,
Alan.

 

[Greg Rivet]

Alan,
=substitute(text,oldtext, newtext,instanceNo). I am using ExcelXL. A
simpler formula would be:
=substitute(A1,5,"S",1).

Greg