Replace data to right of string problem

[Jayjones]

Hi guys - trying to replace or substitute data to almost right o
string ie want excel to always count 5 characters from right of cell bu
then only replace 4 of the characters with XXXX leaving last characte
as it was eg 1234abcde becomes 1234XXXXe or 123456abcdesfgh become
123456abcdXXXXh. I have tried right function with replace but can'
manage to leave last digit intact. The replacing text is always XXX

 

[Spencer101]

Hi guys - trying to replace or substitute data to almost right o
string ie want excel to always count 5 characters from right of cell bu
then only replace 4 of the characters with XXXX leaving last characte
as it was eg 1234abcde becomes 1234XXXXe or 123456abcdesfgh become
123456abcdXXXXh. I have tried right function with replace but can'
manage to leave last digit intact. The replacing text is always XXXX

Hi,

Does the data follow any sort of pattern with regard to how man
characters in length and where abouts in the string the section tha
needs replacing is

 

[Claus Busch]

Hi,

Am Sun, 25 Nov 2012 08:55:01 +0000 schrieb Jayjones:

Hi guys - trying to replace or substitute data to almost right of
string ie want excel to always count 5 characters from right of cell but
then only replace 4 of the characters with XXXX leaving last character
as it was eg 1234abcde becomes 1234XXXXe or 123456abcdesfgh becomes
123456abcdXXXXh. I have tried right function with replace but can't
manage to leave last digit intact. The replacing text is always XXXX

your string in A1:
=LEFT(A1,LEN(A1)-5)&REPT("X",4)&RIGHT(A1,1)


Regards
Claus Busch

 

[Ron Rosenfeld]

Hi guys - trying to replace or substitute data to almost right of
string ie want excel to always count 5 characters from right of cell but
then only replace 4 of the characters with XXXX leaving last character
as it was eg 1234abcde becomes 1234XXXXe or 123456abcdesfgh becomes
123456abcdXXXXh. I have tried right function with replace but can't
manage to leave last digit intact. The replacing text is always XXXX

Also try:

=REPLACE(A1,LEN(A1)-4,4,"xxxx")

 

[Kevin@Radstock]

Hi Jayjones

Assuming your data is in A1:

=SUBSTITUTE(A1,RIGHT(A1,5),"XXXX"&RIGHT(A1,1)) and copy down.

kevin

 

[Jayjones]

Hi,

Does the data follow any sort of pattern with regard to how man
characters in length and where abouts in the string the section tha
needs replacing is?

The data can be various lengths but it's always the last 4 character
but one that needs to be replaced ie I want excel to count from right
characters then replace the next 4 characters leaving the 5 th characte
intac

 

[Claus Busch]

Hi,

you can also try:
=SUBSTITUTE(A1,MID(A1,LEN(A1)-4,4),"XXXX")


Regards
Claus Busch