Replace last three characters

[Phillips via OfficeKB.com]

Hiya! i was wondering if there was a way to replace just the last three
characters in a cell? example i have 123456000 i would like to have instead
of the 000 a 959 so it would be 123456959? any ideas?

 

[ToddEZ]

try the replace function. Use use the formula helper box for the context.

=Replace()

 

[Ron Rosenfeld]

Hiya! i was wondering if there was a way to replace just the last three
characters in a cell? example i have 123456000 i would like to have instead
of the 000 a 959 so it would be 123456959? any ideas?

=REPLACE(A1,LEN(A1)-2,3,959)

This returns a text string, so you may want to precede it with a double unary
to convert it back to a number:

=--REPLACE(A1,LEN(A1)-2,3,959)

If the values are always numbers, you could use this:

=INT(A1/1000)*1000+959


--ron

 

[phillips via OfficeKB.com]

thanks!!!

=REPLACE(A1,LEN(A1)-2,3,959)

This returns a text string, so you may want to precede it with a double unary
to convert it back to a number:

=--REPLACE(A1,LEN(A1)-2,3,959)

If the values are always numbers, you could use this:

=INT(A1/1000)*1000+959

--ron