+ Reply to Thread
Results 1 to 37 of 37

Replacing a word in the middle of a string of text with another

  1. #1
    Forum Contributor
    Join Date
    03-08-2019
    Location
    England
    MS-Off Ver
    2016
    Posts
    104

    Replacing a word in the middle of a string of text with another

    Hello,

    My problem is as follow - I need the second word in the below example (blue, red, green) replaced with the correct word that is in Col B (red, orange, blue) to generate the correct string of text in Col C with the formula

    Col A1/2/3 + Col B1/2/3 = Col C1/2/3 (FORMULA??)

    APPLE BLUE 20/200 KDS RB SOU + Red = APPLE RED 20/200 KDS RB SOU (BLUE REPLACED WITH RED)

    ORANGE RED 30/300 GSH HB SOD + Orange = ORANGE ORANGE 30/300 GSH HB SOD (RED REPLACED WITH ORANGE)

    BLUEBERRY GREEN 50/100 ASD GB AS + Blue = BLUEBERRY BLUE 50/100 ASD GB AS (GREEN REPLACED WITH BLUE)
    Last edited by batexcel; 03-08-2019 at 07:51 AM.

  2. #2
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Replacing a word in the middle of a string of text with another

    one option, based on samples:

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  3. #3
    Forum Contributor
    Join Date
    03-08-2019
    Location
    England
    MS-Off Ver
    2016
    Posts
    104

    Re: Replacing a word in the middle of a string of text with another

    oh my god thank you every so much for that! It look likes it has worked :D

  4. #4
    Forum Contributor
    Join Date
    03-08-2019
    Location
    England
    MS-Off Ver
    2016
    Posts
    104

    Re: Replacing a word in the middle of a string of text with another

    How would the formula work if I wanted to change the 3 letter combination KDS / GSH / ASD with something in B1? And the same question for if I wanted to amend RB / HB / GB with something in Col B?

  5. #5
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Replacing a word in the middle of a string of text with another

    all rather depends on length of elements, and consistency of pattern - but, based solely on examples:

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    would replace the 4th element, and 4* would replace 5th element.

  6. #6
    Forum Contributor
    Join Date
    03-08-2019
    Location
    England
    MS-Off Ver
    2016
    Posts
    104

    Re: Replacing a word in the middle of a string of text with another

    Okay, I think I follow, I shall have a play around to try and develop my understanding on the above - one final question - slightly different but hope you can help, you've been a great help so far.

    Col A1

    ROTH ROYBL 20/200 KRE RB RUS 75.00


    COL B1


    QQ


    = COL C1

    ROTH ROYBL 20/200 KQQ RB RUS 75.00


    So, this time I want to keep the first letter of the 3 letter combination KRE and replace the RE with the new value QQ

  7. #7
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Replacing a word in the middle of a string of text with another

    a little trickier I'd say, but if you're using 2016 then perhaps:

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    in the above change =4 to whichever element you're replacing, the 2 denotes start point for replace (in terms of char pos element [4])

    edit: modified above to use rows 1:7 rather than 1:6 as 7 elements, not 6 {I can't count}
    Last edited by XLent; 03-08-2019 at 10:03 AM.

  8. #8
    Forum Contributor
    Join Date
    03-08-2019
    Location
    England
    MS-Off Ver
    2016
    Posts
    104

    Re: Replacing a word in the middle of a string of text with another

    Genuinely, thank you so much!

  9. #9
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Replacing a word in the middle of a string of text with another

    Quote Originally Posted by batexcel View Post
    Okay, I think I follow, I shall have a play around to try and develop my understanding on the above - one final question - slightly different but hope you can help, you've been a great help so far.
    Col A1
    ROTH ROYBL 20/200 KRE RB RUS 75.00
    COL B1
    QQ
    = COL C1
    ROTH ROYBL 20/200 KQQ RB RUS 75.00
    So, this time I want to keep the first letter of the 3 letter combination KRE and replace the RE with the new value QQ
    Is it simple or I'd missed sth?
    =SUBSTITUTE(A1,"RE",B1)
    Quang PT

  10. #10
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Replacing a word in the middle of a string of text with another

    Hi, based on the thread examples, more specifically post#1 (multiple rows of unique strings with row specific replacement values), I interpreted the requirement as being:

    replace the 2nd character onwards of the 4th element in the string with the value denoted in Column B; apply same logic to multiple rows / replacement values

    so, whilst the above might work for A1 you might have following for row 2 (incl. fact that the string to be replaced [XA in below] may appear multiple times in the string - either partial / complete):

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    so, for that reason I opted for the CONCAT route (c/o XL2016) etc

    not saying it couldn't be done better, but that was the rationale.
    Last edited by XLent; 03-08-2019 at 01:57 PM.

  11. #11
    Forum Contributor
    Join Date
    03-08-2019
    Location
    England
    MS-Off Ver
    2016
    Posts
    104

    Re: Replacing a word in the middle of a string of text with another

    Hello, Yes I have about 1500 lines of data that I have been investigating and amending so the more complex formula was needed rather than the simple substitute.

    I have encountered another issue, and as much as I play with the formula I cannot seem to generate what I require so here I am again and xlent I hope you can continue to be my saviour!

    I need to amend the 2 numbers before the / with 2 numbers from another col


    Col A1 I have


    PALL RED 34/170 KRE SQ GEW TRIAL 7
    PALL RED 35/175 KRE SQ GEW TRIAL10
    ROTH BLUE 25/300 KRE BE UKR 3.0 33.50
    ROTH BLUE 25/300 KRE BE UKR 3.0 34.29

    In Col B1

    40
    40
    20
    20

    In Col C1 I need a formula to make the text read

    PALL RED 40/170 KRE SQ GEW TRIAL 7
    PALL RED 40/175 KRE SQ GEW TRIAL10
    ROTH BLUE 20/300 KRE BE UKR 3.0 33.50
    ROTH BLUE 20/300 KRE BE UKR 3.0 34.29





    Furthermore I need a separate formula to amend the 3 numbers AFTER the / also... so this time I have

    Col A1

    BOYO GREEN 34/170 KRE SQ QWY TREE 9
    PIAI BLUE 35/175 KRE SQ BRE TRIAL 10


    Col B1


    200
    400

    Col C1 I need the string text to read


    BOYO GREEN 34/200 KRE SQ QWY TREE 9
    PIAI BLUE 35/400 KRE SQ BRE TRIAL 10




    Thankfully this is my last hurdle that I have been working on and then I am finally completed my project.

  12. #12
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Replacing a word in the middle of a string of text with another

    based on the samples strings

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    but, they're a little hard-wired for my taste so if you have variants of strings to deal with that the above don't account for post back.

  13. #13
    Forum Contributor
    Join Date
    03-08-2019
    Location
    England
    MS-Off Ver
    2016
    Posts
    104

    Re: Replacing a word in the middle of a string of text with another

    excellent, thank you, I bought an excel book yesterday - should be delivered next couple of days so should be able to aid my knowledge. You've been a great asset to me though xlent

  14. #14
    Forum Contributor
    Join Date
    03-08-2019
    Location
    England
    MS-Off Ver
    2016
    Posts
    104

    Re: Replacing a word in the middle of a string of text with another

    Hello again, another stumbling block I have encountered. I am trying to reduce the number of tables I have and I have been attempting to combine formulas and data etc into one but once again find myself out of my depth. I am not even sure if what I am trying to do is possible but here goes;

    Col A

    Col B



    Col C

    Col D



    Apologies for the formatting - unable to upload a picture of what I have in excel.



    Is there a formula to achieve the above? Any help as always is extremely appreciated and I could not be more grateful!
    Last edited by batexcel; 03-13-2019 at 09:34 AM.

  15. #15
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Replacing a word in the middle of a string of text with another

    I thought this might be coming at some point...

    What I would suggest doing is listing all of your unique Col B values in a separate column, say Col G then in H apply the element you want to replace, and in I the start character in that element from which to begin the replace; where you choose to do nothing apply 0 to Col H, and 1 in Col I -- so, given your examples, you would end up with something like below:

    Please Login or Register  to view this content.
    apols for formatting but you get the idea - 3 columns, Col B value, element # to change (0 if no change), start char # in said element (1 if 0 in prior column)

    with the above in place you can have one formula to do the conversion:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    the results from the above would replicate all of your expected results.
    Last edited by XLent; 03-13-2019 at 07:36 AM. Reason: reworded narrative

  16. #16
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Replacing a word in the middle of a string of text with another

    edit: figured you might prefer to not have to list out every Col B value in this reference table but rather only those where you wish to adjust the string content - refer attached.
    Attached Files Attached Files

  17. #17
    Forum Contributor
    Join Date
    03-08-2019
    Location
    England
    MS-Off Ver
    2016
    Posts
    104

    Re: Replacing a word in the middle of a string of text with another

    Wow, thank you ever so much that is amazing

  18. #18
    Forum Contributor
    Join Date
    03-08-2019
    Location
    England
    MS-Off Ver
    2016
    Posts
    104

    Re: Replacing a word in the middle of a string of text with another

    Hello once again, every time I think I have jumped the final hurdle there seems to be another one a mile down the track.

    I was wondering if the above can be developed for the following.


    Please see the attached looking at Col A and Col F that I have added to what you sent originally.

    So, now I have Col A with material number that aligns to Col B.
    Currently, the formula generates in Col E has worked really well but does not consider if Col B for the same material is changing elsewhere in the document, now I need the formula to generate what I have in Col F.

    Any assistance as always is massively appreciated, thank you for your help so far.
    Attached Files Attached Files
    Last edited by batexcel; 03-14-2019 at 10:09 AM.

  19. #19
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Replacing a word in the middle of a string of text with another

    is there a limit to how many unique values in Col C you could have for a given material number?
    the sample data implies two however I suspect that, in reality, it could be many more than that?

    if the answer to the above is, effectively, 1 to "n" then, whilst I'd have a think about a formula approach, you might want to consider using a Custom (VBA) Function as it will be more efficient.

  20. #20
    Forum Contributor
    Join Date
    03-08-2019
    Location
    England
    MS-Off Ver
    2016
    Posts
    104

    Re: Replacing a word in the middle of a string of text with another

    I do not fully understand your question I am afraid.

    However, Col A could be literally any number up to 7 digits long.

    Only the values in Col I actually impact Col B in any way and the list for Col C is only about 20/30 values total. The Value in Col D could be just about anything. Like Make up quantity 1 has every value from 1 - 999,999 assigned to it.

  21. #21
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Replacing a word in the middle of a string of text with another

    sorry, the question was:

    in your sample no Material # (Col A) appears against more than 2 unique entries in Column C -- is this realistic, or could a Material # appear with, say, 5 unique values in Col C - each of which could, in theory, require a string swap (as defined in table to right)

  22. #22
    Forum Contributor
    Join Date
    03-08-2019
    Location
    England
    MS-Off Ver
    2016
    Posts
    104

    Re: Replacing a word in the middle of a string of text with another

    Ah I follow, in my list of 1845 entries in Col A the most a unique individual number appears is 3 - every time being against a different Col C value

  23. #23
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,409

    Re: Replacing a word in the middle of a string of text with another

    See attachment,
    Ctrl-F3 to see name manager
    FstNum: 1st Qty pos
    Blank1: 1st "blank space" after FstNum
    ....
    Attached Files Attached Files

  24. #24
    Forum Contributor
    Join Date
    03-08-2019
    Location
    England
    MS-Off Ver
    2016
    Posts
    104

    Re: Replacing a word in the middle of a string of text with another

    Hello, thank you so much - the above is brilliant.

    I do not quite understand the name manager part.



    The attached is a little hard wired for my needs though, when I change the information in Col C for example the updated description become wrong. I have highlighted numbers in Col A where I have amended the information in Col C/D or added a new lines and the description does not update accordingly. Not sure if you can advise.


    I will be changing the data in Col A / B / C / D on a daily basis so needs to update as such
    Attached Files Attached Files

  25. #25
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Replacing a word in the middle of a string of text with another

    Per my prior post I would be inclined to go the UDF route, but it might be that your source data is sufficiently limited that the approach offered in prior post will suffice?

    Below would be one possible UDF, this should work irrespective of how your data is sorted, and/or how many variants you need to account for in a given material no.
    Caveat: the below does assume that when swapping out 1+ strings within same element {e.g. elements 3 & 4} that the before/after values are like-for-like in terms of their respective length - e.g. 20&30 rather than 30&100

    Please Login or Register  to view this content.
    the above would be stored in a Module in VBE, and could then be called from your cells along lines of:

    Formula: copy to clipboard
    Please Login or Register  to view this content.

    note: I inserted a blank row 1 just to limit how many times you call the UDF -- i.e. it will only fire the first time it finds an item number, else it will use whatever result it conjured previously.
    Attached Files Attached Files
    Last edited by XLent; 03-15-2019 at 05:09 AM.

  26. #26
    Forum Contributor
    Join Date
    03-08-2019
    Location
    England
    MS-Off Ver
    2016
    Posts
    104

    Re: Replacing a word in the middle of a string of text with another

    My hero. I am trying to break your work but it is working every single time, amazing

  27. #27
    Forum Contributor
    Join Date
    03-08-2019
    Location
    England
    MS-Off Ver
    2016
    Posts
    104

    Re: Replacing a word in the middle of a string of text with another

    When I replace a 4 of 5 letter word for local brand family group (the second word in the string of text) with a 3 letter word only the first 3 letters are replaced and then the 4/5th letters are kept, i.e.

    Col B
    BLUE

    Col D
    RED

    COL E

    REDE


    I know you said this in your original post but is there a solution to fix this issue?


    I had previously thought this was a non issue because when the shorter word was being replaced by the longer word the full text would transfer over so RED replaced by BLUE would end up with BLUE. Rather than 'blu'. Is there a way to prevent RED replacing BLUE ending up with REDE
    Attached Files Attached Files
    Last edited by batexcel; 03-15-2019 at 01:29 PM.

  28. #28
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Replacing a word in the middle of a string of text with another

    For those elements that necessitate only 1 swap you could modify easily enough (below).

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by XLent; 03-16-2019 at 06:53 AM.

  29. #29
    Forum Contributor
    Join Date
    03-08-2019
    Location
    England
    MS-Off Ver
    2016
    Posts
    104

    Re: Replacing a word in the middle of a string of text with another

    Hello, thank you, sorry I did not have access to internet for a few days otherwise would have thanked you sooner.

    Would the same be possible on make up quantity values? That is the final element to my problem now

    In the attached Col E does not align with what it needs to be in Col F. Thank you in advance as always
    Attached Files Attached Files
    Last edited by batexcel; 03-18-2019 at 11:34 AM.

  30. #30
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Replacing a word in the middle of a string of text with another

    Revised UDF in attached - note additional column added to reference table.
    Attached Files Attached Files

  31. #31
    Forum Contributor
    Join Date
    03-08-2019
    Location
    England
    MS-Off Ver
    2016
    Posts
    104

    Re: Replacing a word in the middle of a string of text with another

    Again thank you, wish I knew what I needed to do in the first place to have saved you all this time but I am genuinely so appreciative

  32. #32
    Forum Contributor
    Join Date
    03-08-2019
    Location
    England
    MS-Off Ver
    2016
    Posts
    104

    Re: Replacing a word in the middle of a string of text with another

    Hello again,
    (formula in col F)

    I had the following formula from you give or take; (old formula)

    =IFERROR(VLOOKUP($B3,$B$2:$F2,5,0),SWAPSHOP(B3,$B$3:$E$500,$I$3:$L$9)))

    This formula would always return a value in Col F regardless of if Col E was blank or not.

    I amended the formula to; (new formula)

    =IF(E3="","",IFERROR(VLOOKUP($B3,$B$2:$F2,5,0),SWAPSHOP(B3,$B$3:$E$500,$I$3:$L$9)))

    This now means that if Col E is blank then Col F also remains blank.

    However this is not quite my desire.

    I have a series of codes in Col B, sometimes they are identical. If Col C there will be a corresponding code description. If there is information in Col E then then formula is supposed to return a new code description - however now even if there is a value in E4 and E5 for material 100 because E3 is blank for code 100 the new code description does not update in Col F.


    Please see attached for a visual of what I am trying to explain as I am struggling a fair bit to explain (I have knocked out all formulas and put this on a new spreadsheet to what I am really working on)

    I have tried my best to make sense here - tricky to explain
    Attached Files Attached Files

  33. #33
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Replacing a word in the middle of a string of text with another

    without seeing the file inclusive of UDF, lookup tables and parameters it's hard to give any definitive solution, here.

    based on what you have outlined, in terms of sample data set, you could remove the outer IFERROR(VLOOKUP e.g.:

    =IF(E3="","",SWAPSHOP(B3,$B$3:$E$500,$I$3:$L$9))

    however, this could have a notable impact on overall performance pending size of your real-life precedent ranges

    if the above is, indeed, an issue you could try below modification:

    =IF($E3="","",IFERROR(INDEX($F:$F,AGGREGATE(15,6,ROW($E$2:$E2)/($B$2:$B2=$B3)/($F$2:$F2<>""),1)),SWAPSHOP(B3,$B$3:$E$500,$I$3:$L$9)))

    the above will not calculate the SWAPSHOP value if it can find a valid result for that code in any prior row

  34. #34
    Forum Contributor
    Join Date
    03-08-2019
    Location
    England
    MS-Off Ver
    2016
    Posts
    104

    Re: Replacing a word in the middle of a string of text with another

    This looks to have worked, thank you!

  35. #35
    Forum Contributor
    Join Date
    03-08-2019
    Location
    England
    MS-Off Ver
    2016
    Posts
    104

    Re: Replacing a word in the middle of a string of text with another

    Is it possible to amend the formula further so that if the result returns #value! it simply appears blank / empty?

    amazing work as always

  36. #36
    Forum Expert XLent's Avatar
    Join Date
    10-13-2010
    Location
    Northumberland, UK
    MS-Off Ver
    various
    Posts
    2,704

    Re: Replacing a word in the middle of a string of text with another

    simplest to apply a further IFERROR to your existing calc, which would cater for possibility of E3 being invalid and SWAPSHOP returning invalid response

    so, simply

    =IFERROR(< existing formula >,"")

  37. #37
    Forum Contributor
    Join Date
    03-08-2019
    Location
    England
    MS-Off Ver
    2016
    Posts
    104

    Re: Replacing a word in the middle of a string of text with another

    oh course, I actually knew that one too.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Return word in middle of text
    By Iraokusaicaro in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-20-2018, 07:44 AM
  2. Extract string of text in the middle of 2 delimiters
    By MoldyBread in forum Excel General
    Replies: 3
    Last Post: 05-07-2018, 07:21 AM
  3. Selecting text from the middle of a string
    By hammerguy2 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 09-08-2016, 07:45 PM
  4. Pick out text from middle of a string
    By aaron.phelan12345 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-06-2015, 01:00 PM
  5. Sort by text in the middle of a string
    By javeds in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-08-2014, 12:32 PM
  6. [SOLVED] if formula looking at value in middle of text string
    By SAsplin in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 04-25-2013, 06:57 AM
  7. [SOLVED] pulling text from the middle of a string
    By DRFILL in forum Excel General
    Replies: 3
    Last Post: 09-21-2012, 11:35 AM

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