+ Reply to Thread
Results 1 to 20 of 20

Replacing one character in variable position in a text string

  1. #1
    Registered User
    Join Date
    08-07-2012
    Location
    Germany
    MS-Off Ver
    Excel 2011 Mac
    Posts
    11

    Replacing one character in variable position in a text string

    Hello,

    I'm working with a large set of alphanumeric strings like these:

    SO2TTO3SKA1LA3
    SO2VRA3PPJU7`
    SPA2KKA3PJE7TRE3
    SPO1RT
    SPU3NTA1RE3

    They contain single-digit numbers as well as letters. I need to replace the third number from the RIGHT with "9" if:
    1) the third number from the right is a 3, the second number from the right a 1, and the first number from the right again a 3 AND
    2) there are at least three letters between the third and the second number from the right except if the letter immediately following the third number from the right is an S.

    Thus, in the set above, the fifth string would change to SPU9NTA1RE3. All other ones would remain unchanged (the first one, SO2TTO3SKA1LA3, has the pattern 3-1-3 and three letters between 3 and 1 but the first of those is an S).

    Any help would be greatly appreciated!

    Many thanks
    Last edited by toontopo; 08-14-2012 at 08:51 AM.

  2. #2
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: Replacing one character in variable position in a text string

    The only reasonable way I can see of doing this is with a user-defined function (UDF):

    Please Login or Register  to view this content.
    Putting this code into a module in your workbook will let you use StrangeReplace as a function, so if your string was in A1 in B1 you could have:

    =STRANGEREPLACE(A1)

    Does that help?

  3. #3
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Replacing one character in variable position in a text string

    With
    Col_A containing text strings
    SO2TTO3SKA1LA3
    SO2VRA3PPJU7
    SPA2KKA3PJE7TRE3
    SPO1RT
    SPU3NTA1RE3
    SPU3NTA1REZZZ3

    If a regular formula is acceptable...
    try this
    Please Login or Register  to view this content.
    Copy that formula down as far as you need

    In the above example, the formulas return:
    SO2TTO3SKA1LA3
    SO2VRA3PPJU7
    SPA2KKA3PJE7TRE3
    SPO1RT
    SPU9NTA1RE3 <----Changed
    SPU9NTA1REZZZ3 <----Changed


    Is that something you can work with?
    Last edited by Ron Coderre; 08-14-2012 at 10:15 AM.
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  4. #4
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: Replacing one character in variable position in a text string

    Damn you, Ron, I was busy working out my own formula solution.

    Admittedly mine wasn't *quite* as compact as yours:

    =IF(SUMPRODUCT(--(ISNUMBER(VALUE(MID(A5,ROW(INDIRECT("1:" & LEN(A5))),1)))))<3,A5,IF(MID(A5,LEN(A5)-FIND("1",TEXT(SUMPRODUCT(--(ISNUMBER(VALUE(MID(A5,ROW(INDIRECT("1:" & LEN(A5))),1))))*10^ROW(INDIRECT("1:" & LEN(A5)))),REPT("0",LEN(A5))))+1,1)<>"3",A5,IF(MID(A5,LEN(A5)-FIND("~",SUBSTITUTE(TEXT(SUMPRODUCT(--(ISNUMBER(VALUE(MID(A5,ROW(INDIRECT("1:" & LEN(A5))),1))))*10^ROW(INDIRECT("1:" & LEN(A5)))),REPT("0",LEN(A5))),"1","~",2))+1,1)<>"1",A5,IF(MID(A5,LEN(A5)-FIND("~",SUBSTITUTE(TEXT(SUMPRODUCT(--(ISNUMBER(VALUE(MID(A5,ROW(INDIRECT("1:" & LEN(A5))),1))))*10^ROW(INDIRECT("1:" & LEN(A5)))),REPT("0",LEN(A5))),"1","~",3))+1,1)<>"3",A5,IF(MID(A5,LEN(A5)-FIND("~",SUBSTITUTE(TEXT(SUMPRODUCT(--(ISNUMBER(VALUE(MID(A5,ROW(INDIRECT("1:" & LEN(A5))),1))))*10^ROW(INDIRECT("1:" & LEN(A5)))),REPT("0",LEN(A5))),"1","~",3))+2,1)="S",A5,IF(FIND("~",SUBSTITUTE(TEXT(SUMPRODUCT(--(ISNUMBER(VALUE(MID(A5,ROW(INDIRECT("1:" & LEN(A5))),1))))*10^ROW(INDIRECT("1:" & LEN(A5)))),REPT("0",LEN(A5))),"1","~",3))-FIND("~",SUBSTITUTE(TEXT(SUMPRODUCT(--(ISNUMBER(VALUE(MID(A5,ROW(INDIRECT("1:" & LEN(A5))),1))))*10^ROW(INDIRECT("1:" & LEN(A5)))),REPT("0",LEN(A5))),"1","~",2))<3,A5,LEFT(A5, LEN(A5)-FIND("~",SUBSTITUTE(TEXT(SUMPRODUCT(--(ISNUMBER(VALUE(MID(A5,ROW(INDIRECT("1:" & LEN(A5))),1))))*10^ROW(INDIRECT("1:" & LEN(A5)))),REPT("0",LEN(A5))),"1","~",3))) & "9" & MID(A5,LEN(A5)-FIND("~",SUBSTITUTE(TEXT(SUMPRODUCT(--(ISNUMBER(VALUE(MID(A5,ROW(INDIRECT("1:" & LEN(A5))),1))))*10^ROW(INDIRECT("1:" & LEN(A5)))),REPT("0",LEN(A5))),"1","~",3))+2,255)))))))

    Truly you are the god of formula.

  5. #5
    Registered User
    Join Date
    08-07-2012
    Location
    Germany
    MS-Off Ver
    Excel 2011 Mac
    Posts
    11

    Re: Replacing one character in variable position in a text string

    Sorry, haven't looked at the other posts. Will be back soon...
    Last edited by toontopo; 08-14-2012 at 10:19 AM.

  6. #6
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Replacing one character in variable position in a text string

    Quote Originally Posted by Andrew-R View Post
    Damn you, Ron, I was busy working out my own formula solution.

    Admittedly mine wasn't *quite* as compact as yours:

    =IF(SUMPRODUCT(--(ISNUMBER(VALUE(MID(A5,ROW(INDIRECT("1:" & LEN(A5))),1)))))<3,A5,IF(MID(A5,LEN(A5)-FIND("1",TEXT(SUMPRODUCT(--(ISNUMBER(VALUE(MID(A5,ROW(INDIRECT("1:" & LEN(A5))),1))))*10^ROW(INDIRECT("1:" & LEN(A5)))),REPT("0",LEN(A5))))+1,1)<>"3",A5,IF(MID(A5,LEN(A5)-FIND("~",SUBSTITUTE(TEXT(SUMPRODUCT(--(ISNUMBER(VALUE(MID(A5,ROW(INDIRECT("1:" & LEN(A5))),1))))*10^ROW(INDIRECT("1:" & LEN(A5)))),REPT("0",LEN(A5))),"1","~",2))+1,1)<>"1",A5,IF(MID(A5,LEN(A5)-FIND("~",SUBSTITUTE(TEXT(SUMPRODUCT(--(ISNUMBER(VALUE(MID(A5,ROW(INDIRECT("1:" & LEN(A5))),1))))*10^ROW(INDIRECT("1:" & LEN(A5)))),REPT("0",LEN(A5))),"1","~",3))+1,1)<>"3",A5,IF(MID(A5,LEN(A5)-FIND("~",SUBSTITUTE(TEXT(SUMPRODUCT(--(ISNUMBER(VALUE(MID(A5,ROW(INDIRECT("1:" & LEN(A5))),1))))*10^ROW(INDIRECT("1:" & LEN(A5)))),REPT("0",LEN(A5))),"1","~",3))+2,1)="S",A5,IF(FIND("~",SUBSTITUTE(TEXT(SUMPRODUCT(--(ISNUMBER(VALUE(MID(A5,ROW(INDIRECT("1:" & LEN(A5))),1))))*10^ROW(INDIRECT("1:" & LEN(A5)))),REPT("0",LEN(A5))),"1","~",3))-FIND("~",SUBSTITUTE(TEXT(SUMPRODUCT(--(ISNUMBER(VALUE(MID(A5,ROW(INDIRECT("1:" & LEN(A5))),1))))*10^ROW(INDIRECT("1:" & LEN(A5)))),REPT("0",LEN(A5))),"1","~",2))<3,A5,LEFT(A5, LEN(A5)-FIND("~",SUBSTITUTE(TEXT(SUMPRODUCT(--(ISNUMBER(VALUE(MID(A5,ROW(INDIRECT("1:" & LEN(A5))),1))))*10^ROW(INDIRECT("1:" & LEN(A5)))),REPT("0",LEN(A5))),"1","~",3))) & "9" & MID(A5,LEN(A5)-FIND("~",SUBSTITUTE(TEXT(SUMPRODUCT(--(ISNUMBER(VALUE(MID(A5,ROW(INDIRECT("1:" & LEN(A5))),1))))*10^ROW(INDIRECT("1:" & LEN(A5)))),REPT("0",LEN(A5))),"1","~",3))+2,255)))))))

    Truly you are the god of formula.
    Yikes! My computer monitor sagged trying to hold up that formula!
    (Thanks for the kind words)

    ---------- Post added at 10:24 AM ---------- Previous post was at 10:17 AM ----------

    Quote Originally Posted by toontopo View Post
    Hi,
    many thanks for taking to the trouble of thinking of a solution to what must look totally weird. When putting your code into my workbook, I get the the NAME? error though. Could I have overlooked something very basic?
    That error indicates that either
    a function name is entered incorrectly
    ...fix by editing the function name

    or...perhaps because you have MAC
    you don't have the IFERROR function.
    ...fix by using this variation of the formula I posted
    Please Login or Register  to view this content.
    Does that help?

  7. #7
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Replacing one character in variable position in a text string

    Ummmm...How did my reply to toontopo end up getting appended to Andrew-R's post?

    ---------- Post added at 10:38 AM ---------- Previous post was at 10:37 AM ----------

    Quote Originally Posted by toontopo View Post
    Sorry, haven't looked at the other posts. Will be back soon...
    Somehow my reply to you got appended to my reply to Andrew-R's post. I have no idea how that happened.

  8. #8
    Forum Expert
    Join Date
    07-16-2010
    Location
    Northumberland, UK
    MS-Off Ver
    Excel 2007 (home), Excel 2010 (work)
    Posts
    3,054

    Re: Replacing one character in variable position in a text string

    I think my formula broke the forum

  9. #9
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Replacing one character in variable position in a text string

    Quote Originally Posted by toontopo View Post
    Sorry, haven't looked at the other posts. Will be back soon...
    One more try...my posts are getting appended to my replies to Andrew-r. I don't know why.

    ---------- Post added at 10:45 AM ---------- Previous post was at 10:45 AM ----------

    Quote Originally Posted by Andrew-R View Post
    I think my formula broke the forum
    THAT's funny!

  10. #10
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013 O.365
    Posts
    20,050

    Re: Replacing one character in variable position in a text string

    UDF

    =Replace3(A1)

    Please Login or Register  to view this content.
    Last edited by jindon; 08-14-2012 at 10:58 AM.

  11. #11
    Registered User
    Join Date
    08-07-2012
    Location
    Germany
    MS-Off Ver
    Excel 2011 Mac
    Posts
    11

    Re: Replacing one character in variable position in a text string

    Hi Ron, many thanks for this, I'm in awe! Indeed I have a Mac. Unfortunately though, I still get the error message "The formula you typed contained an error" even with your new formula. Thank you again.

  12. #12
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Replacing one character in variable position in a text string

    Quote Originally Posted by toontopo View Post
    Hi Ron, many thanks for this, I'm in awe! Indeed I have a Mac. Unfortunately though, I still get the error message "The formula you typed contained an error" even with your new formula. Thank you again.
    Can you post a sample workbook with the formula I posted, so we can see what's going wrong?

  13. #13
    Registered User
    Join Date
    08-07-2012
    Location
    Germany
    MS-Off Ver
    Excel 2011 Mac
    Posts
    11

    Re: Replacing one character in variable position in a text string

    Here it is , without your formula though because the computer wouldn't let me save it with it.
    Attached Files Attached Files

  14. #14
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Replacing one character in variable position in a text string

    Quote Originally Posted by toontopo View Post
    Here it is , without your formula though because the computer wouldn't let me save it with it.
    Here you go...see if the attached workbook helps.
    Attached Files Attached Files

  15. #15
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Replacing one character in variable position in a text string

    Ron's original formula does work in Excel 2011. Sample attached.
    Attached Files Attached Files
    Remember what the dormouse said
    Feed your head

  16. #16
    Registered User
    Join Date
    08-07-2012
    Location
    Germany
    MS-Off Ver
    Excel 2011 Mac
    Posts
    11

    Re: Replacing one character in variable position in a text string

    Brilliant, thank you so much guys! Also many thanks to jindon.

  17. #17
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Replacing one character in variable position in a text string

    Quote Originally Posted by romperstomper View Post
    Ron's original formula does work in Excel 2011. Sample attached.
    Thanks, R.
    BTW...no need for the array formulas. The regular formula works just fine...(Unless: Is C+S+E required in Excel 2011?)

  18. #18
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Replacing one character in variable position in a text string

    No - I just saw the MID(...ROW(INDIRECT(..)) construct and my fingers automatically did the CSE.

  19. #19
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676

    Re: Replacing one character in variable position in a text string

    Am I reading the requirement wrong? If the cell value is TO3BO1GA3 should that change?, surely there are only 2 characters between the third and second numbers from the right?
    Audere est facere

  20. #20
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Replacing one character in variable position in a text string

    Quote Originally Posted by daddylonglegs View Post
    Am I reading the requirement wrong? If the cell value is TO3BO1GA3 should that change?, surely there are only 2 characters between the third and second numbers from the right?
    DL...Nice catch! Thanks. You're absolutely right.
    The formula I posted has no test for 3-or-more-letters between the 3rd and 2nd digits from the right.
    This regular formula corrects that oversight:
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1