Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
Quote:
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
|
|||
|
|||
Hi Jayjones
Assuming your data is in A1: =SUBSTITUTE(A1,RIGHT(A1,5),"XXXX"&RIGHT(A1,1)) and copy down. kevin Quote:
|
#6
|
|||
|
|||
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
|
|||
|
|||
Thats what I have just given you is'nt it !
|
#8
|
|||
|
|||
Hi Kevin, thank you so much - wouldn't have thought about combination. Wish I had posted this sooner!
|
#9
|
|||
|
|||
No problem, glad your sorted.
|
#10
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
replace string | Excel Programming | |||
Chartspace Data Source and Connection String Problem | Excel Programming | |||
Replace Hyperlink Addresses Help 'Dim OldStr As String, NewStr As String | Excel Programming | |||
How do I replace last numeric string from a alphanumeric string? | Excel Discussion (Misc queries) | |||
Problem with search and replace data,thanks for you help in advance. | Excel Discussion (Misc queries) |