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?

=REPLACE(A1,10,1," ")
I'm assuming that the 'S' will always be the 10th character or this wont
work,
Regards,
"McDal" wrote in message
...
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?

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)


"McDal" wrote:

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?

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?

"Duke Carey" wrote:

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)


"McDal" wrote:

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?

yes it is the 10th character. Where do I put this =replace code?

"Alan" wrote:

=REPLACE(A1,10,1," ")
I'm assuming that the 'S' will always be the 10th character or this wont
work,
Regards,
"McDal" wrote in message
...
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?

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,
"McDal" wrote in message
...
yes it is the 10th character. Where do I put this =replace code?

"Alan" wrote:

=REPLACE(A1,10,1," ")
I'm assuming that the 'S' will always be the 10th character or this wont
work,
Regards,
"McDal" wrote in message
...
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?