Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Junior Member
 
Posts: 4
Default Replace data to right of string problem

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
  #2   Report Post  
Senior Member
 
Posts: 663
Default

Quote:
Originally Posted by Jayjones View Post
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?
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default Replace data to right of string problem

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
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 1,045
Default Replace data to right of string problem

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

  #5   Report Post  
Member
 
Posts: 93
Default

Hi Jayjones

Assuming your data is in A1:

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

kevin


Quote:
Originally Posted by Jayjones View Post
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


  #6   Report Post  
Junior Member
 
Posts: 4
Default

Quote:
Originally Posted by Spencer101 View Post
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
  #7   Report Post  
Member
 
Posts: 93
Default

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

Quote:
Originally Posted by Jayjones View Post
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
  #8   Report Post  
Junior Member
 
Posts: 4
Default

Quote:
Originally Posted by Kevin@Radstock View Post
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!
  #9   Report Post  
Member
 
Posts: 93
Default

No problem, glad your sorted.

Quote:
Originally Posted by Jayjones View Post
Hi Kevin, thank you so much - wouldn't have thought about combination. Wish I had posted this sooner!
  #10   Report Post  
Junior Member
 
Posts: 4
Default

Quote:
Originally Posted by Kevin@Radstock View Post
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!


  #11   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,872
Default Replace data to right of string problem

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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
replace string hoysala Excel Programming 3 January 26th 08 12:24 AM
Chartspace Data Source and Connection String Problem headly Excel Programming 0 January 23rd 08 01:36 AM
Replace Hyperlink Addresses Help 'Dim OldStr As String, NewStr As String Ron[_14_] Excel Programming 6 January 23rd 07 08:38 PM
How do I replace last numeric string from a alphanumeric string? Christy Excel Discussion (Misc queries) 3 August 11th 06 12:17 AM
Problem with search and replace data,thanks for you help in advance. yoyo2000 Excel Discussion (Misc queries) 1 June 20th 06 03:56 AM


All times are GMT +1. The time now is 03:20 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"