With your data in A1:A1000, in say B1

=LEFT(A1,4)&"2"&RIGHT(A1,LEN(A1)-5)

and copy down. Just change what's between the " " for what you want.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"photowiz" wrote in message
...
HI
Is there a way to copy the contents of one column to another, but change
say
the fifth character of each cell to a different character? For instance, I
have a value of MLPM1 in a column (in each cell) with repetitive data and
want to copy that column but change the 5th character to "2" so that it
reads
MLPM2-(data). Can anybody help? Thanks in advance!

Scott