+ Reply to Thread
Page 2 of 3 FirstFirst 1 2 3 LastLast
Results 16 to 30 of 31

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

  1. #16
    Valued Forum Contributor
    Join Date
    10-13-2010
    Location
    UK
    MS-Off Ver
    various
    Posts
    717

    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

  2. #17
    Registered User
    Join Date
    03-08-2019
    Location
    England
    MS-Off Ver
    2016
    Posts
    37

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

    Wow, thank you ever so much that is amazing

  3. #18
    Registered User
    Join Date
    03-08-2019
    Location
    England
    MS-Off Ver
    2016
    Posts
    37

    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.

  4. #19
    Valued Forum Contributor
    Join Date
    10-13-2010
    Location
    UK
    MS-Off Ver
    various
    Posts
    717

    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.

  5. #20
    Registered User
    Join Date
    03-08-2019
    Location
    England
    MS-Off Ver
    2016
    Posts
    37

    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.

  6. #21
    Valued Forum Contributor
    Join Date
    10-13-2010
    Location
    UK
    MS-Off Ver
    various
    Posts
    717

    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)

  7. #22
    Registered User
    Join Date
    03-08-2019
    Location
    England
    MS-Off Ver
    2016
    Posts
    37

    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

  8. #23
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2007
    Posts
    4,803

    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

  9. #24
    Registered User
    Join Date
    03-08-2019
    Location
    England
    MS-Off Ver
    2016
    Posts
    37

    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

  10. #25
    Valued Forum Contributor
    Join Date
    10-13-2010
    Location
    UK
    MS-Off Ver
    various
    Posts
    717

    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.

  11. #26
    Registered User
    Join Date
    03-08-2019
    Location
    England
    MS-Off Ver
    2016
    Posts
    37

    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

  12. #27
    Registered User
    Join Date
    03-08-2019
    Location
    England
    MS-Off Ver
    2016
    Posts
    37

    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.

  13. #28
    Valued Forum Contributor
    Join Date
    10-13-2010
    Location
    UK
    MS-Off Ver
    various
    Posts
    717

    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.

  14. #29
    Registered User
    Join Date
    03-08-2019
    Location
    England
    MS-Off Ver
    2016
    Posts
    37

    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.

  15. #30
    Valued Forum Contributor
    Join Date
    10-13-2010
    Location
    UK
    MS-Off Ver
    various
    Posts
    717

    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

+ Reply to Thread
Page 2 of 3 FirstFirst 1 2 3 LastLast

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