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

Hi,

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

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

Thats what I have just given you is'nt it !

Sorry Kevin I responded to last message before seeing yours - still new to these kinds of sites - again really appreciate your solution:) - saved me so much time!

Hi Jayjones

Assuming your data is in A1:

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

kevin

Hi Kevin, thank you so much - wouldn't have thought about combination. Wish I had posted this sooner!

No problem, glad your sorted.

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

On Sun, 25 Nov 2012 08:55:01 +0000, Jayjones wrote:


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")

Hi,

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


Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2