Replacing a specific position in a cell with something else

M

McDal

I need to replace in all my thousands of part numbers.....position number 10
which is the letter "S" to a blank.

Does anybody know how to do this?
 
A

Alan

=REPLACE(A1,10,1," ")
I'm assuming that the 'S' will always be the 10th character or this wont
work,
Regards,
 
D

Duke Carey

If the only S to be found in your part #s is the one to replace, or if it is
ALWAYS the first S in the #, use

=SUBSTITUTE(prt#,"S"," ",1)

otherwise use

=left(prt#,9)&" "&right(prt#,len(prt#)-10)
 
M

McDal

Thanks for the info...but I'll ask the next stupid question...where do I put
this code? And my letter S in my part number column in the excel sheet is
always in position 10. Do I have to format the cell to be text or a number
field? And, how do I get it not to truncate leading zeros?
 
A

Alan

If your part numbers are in column A, say A1:A5000, then in B1 enter
=REPLACE(A1,10,1," ")
Drag this formula down to the end of your range, A5000 in this example,
This will give you in column B your part numbers with the 'S' replaced by a
space.
You may then want to get rid of the formulas and leave just the values, if
so, highlight the whole column , right click in the highlighted area and go
'Copy', right click in the area again, go 'Paste Special', check 'Values',
OK
Regards,
 
Top