+ Reply to Thread
Results 1 to 5 of 5

Find 3 character value throughout concatened string and replace false matches with blank

  1. #1
    Forum Contributor
    Join Date
    01-19-2021
    Location
    Brazil
    MS-Off Ver
    Office 365
    Posts
    263

    Question Find 3 character value throughout concatened string and replace false matches with blank

    Hi,

    I'm trying to make a dynamic array with a bunch of concatened SKUs. The codes are built merging different other codes separated by a letter with each block representing some info. The first block holds the first 3 numbers and ends by the letter "M", and corresponds to the brand of the product. What I'm trying to do is: based on a column holding each and every brand code and according to an adjacent column carrying true/false statements for each of these codes, I thought maybe I could look through all concatenated value in a string and deactivate all codes marked as false.

    If it was a single value I think I could come up with something maybe using LEFT, SUBSTITUTE and maybe INDEX to check an array, but I have no idea how to achieve this with multiple concatenated values that vary in quantity (though separated by ";" and having the same lenght for each code block).

    I'll be really thankful if anyone could help me out, maybe at least pointing out a way.

    I'm attaching a sample represeting the situation.
    Attached Files Attached Files
    Pedro.

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,572

    Re: Find 3 character value throughout concatened string and replace false matches with bla

    "... deactivate all codes marked as false ..."
    What does deactivate mean?
    What would be the final result?
    Ben Van Johnson

  3. #3
    Forum Contributor
    Join Date
    01-19-2021
    Location
    Brazil
    MS-Off Ver
    Office 365
    Posts
    263

    Re: Find 3 character value throughout concatened string and replace false matches with bla

    Hello proton,

    Thanks for the reply

    By deactivate I mean turning that specific code block into blank (including the ";"), so if I have: AAA111BBB; BBBAAA222; CCCBBB333, and I wanted to deactivate the last block, the result would be: AAA111BBB; BBBAAA222;

    The trick is making it don't substitute if the respective first 3 digits are marked as true in the respective reference cell



    EDIT: edit example to make more sense
    Last edited by therealdees; 04-19-2021 at 09:08 PM.

  4. #4
    Forum Contributor
    Join Date
    01-19-2021
    Location
    Brazil
    MS-Off Ver
    Office 365
    Posts
    263

    Re: Find 3 character value throughout concatened string and replace false matches with bla

    I updated the sheet with the simulated result according to the true/false situation displayed.

    Every code which the first 3 digits are marked as false in column D was erased from the chain. If the value turns to be true, then the value should show up again
    Attached Files Attached Files
    Last edited by therealdees; 04-19-2021 at 09:01 PM.

  5. #5
    Forum Contributor
    Join Date
    01-19-2021
    Location
    Brazil
    MS-Off Ver
    Office 365
    Posts
    263

    Re: Find 3 character value throughout concatened string and replace false matches with bla

    BUMP


    sos plz

+ 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] Replace Characters to Left of a String with Another String/Character
    By stevoDE in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-06-2019, 02:43 AM
  2. [SOLVED] how to find special character & replace with blank
    By mohan.r1980 in forum Excel General
    Replies: 7
    Last Post: 08-31-2016, 12:30 PM
  3. replace EACH Nth occurrence a character in a string with another character
    By mattman7 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 06-23-2016, 12:23 PM
  4. How can I replace a character in a string ?
    By dschmitt in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-07-2015, 08:59 AM
  5. [SOLVED] find the character position in a string of the last occurrence of a nominated character
    By jmac1947 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-04-2014, 11:52 PM
  6. Replace only one character in string.
    By ali84pk in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 02-24-2012, 04:39 PM
  7. VBA Find matches one character at a time
    By nms2130 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-10-2011, 12:17 PM

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