SOLVED

Search for (an exact?) match in C, that is contained in the text on B.

Copper Contributor

Hello, community!

I've hit a wall trying to find a solution to a problem. I'll do my best to try to explain what I want to achieve, and what I've thought of/done thus far, but I can't give you the actual (job) data, so I came up with some pseudo-lorem ipsum, that sums up every possible combination and matches the overall criteria of the data.

I have the following table (Table6:(

JorgeE570_1-1695245337873.png

 

What I need is to get the data from [Original String] kind of split into two columns

At the same time, there is no fixed criteria (or at least I can't see one). The closest I can get to a fixed criteria is that [Result 2] will always be determined by the last few characters from [Original String], and that most of the time, part of the text in [Result 2] will match part of the text in [[Original String].

 

So, to keep it short: I thought of making a table that lists the text to cut from [Original String] (Table5:(

JorgeE570_2-1695245539416.png

It would still be helpful, as this list would be long, but manageable to be manually maintained.

So, what I need is to get each cell from [Original String] to search for a cell on [Cut down strings], which content fully matches the beginning of the original text, and then print [Improved strings] in [Result 1] and what's outside the match, be printed in [Result 2].

I hope that makes sense.

 

This is the monstrosity I came up with to get [Result 1]: =VLOOKUP(MID(Table6[Original string],1,LEN(Table5[Cut down strings])),Table5,2,FALSE)

The issue I'm having with that, is that I think it's returning an array (is that what a list of results is called?) for each cell in [Original String].

This is getting a bit over the top, but summing up, I can make it work if there is a way to only get the first true match from that formula, but I can't find how to do so. I think that having that, then removing said text from [Original String] to display what's left on [Result 2] shouldn't be too hard.

 

If you think there is a better way to get all of this done, feel free to even just throw me the name of a formula or tool! I'm more than willing to keep trying, but I'll have to leave it for today, or my brain will explode lol. Also, the data can be

 

I hope the idea is somewhat clear, but let me know if something doesn't make sense, or if there's some necessary information missing, and I'll do my best to clarify (English is not my native language).

 

Thanks in advance!

5 Replies

@JorgeE570 

Does the attached suggestion help you? The formulas in the file have to be entered with ctrl+shift+ enter if one doesn't work with Office 365 or Excel for the web or Excel 2021.

@OliverScheurich 

Wow, that was fast!
I didn't even think of attaching a file haha

Thank you so much!!!

One small kink I got when I got the formula into the table, is this:

JorgeE570_1-1695250197538.png

 

The highlighted rows show "-1", "-16" and "X - 2". Only the last one is correct, but the first two should be "1" and "16". I tried this change, but I think I broke the logic:

JorgeE570_2-1695250345078.png

Maybe you have an idea on how to fix it?

 

Again, thank you so much!!

This really helps a lot.

After reading a bit about what you said about CTRL + SHIFT + ENTER, I think I got why I should use that, but it doesn't seem to work when I modified the formula for the table. It does work in the file you sent, but on the table, I get the formula written if I convert it to an array.

JorgeE570_0-1695251988757.png


I don't think I changed the logic of the formula you made, just the source of the data, but for some reason, that won't work. That being said, without doing that, it luckily seems to work in a table as a simple formula.

best response confirmed by JorgeE570 (Copper Contributor)
Solution

@JorgeE570 

In my first reply i didn't realize that in column  [Cut down strings] there are dashes in the end in the first 2 rows: "This is a string -" and "This is another string -". However i'd suggest to use a table without dashes like this: "This is a string" and "This is another string". In the attached file i've changed the formula for column [Result 2] to return the results "1" and "16" and "X - 2".

cut down strings.png

I don't know why it doesn't work in the other file. I'd delete both tables of the other file and start over.

Amazing!
I'll be honest, it's quite late here, and I don't think I'll get those formulas. I'll take a look at them tomorrow to try and figure out how it works. Nevertheless, I can confirm that these work perfectly fine!
I can't help but ask: In the last file you sent, the curly braces (formula as an array? I have a VERY basic understanding of this, as I'd never heard about it, and had some 10 minutes of search on the topic) don't seem to do anything. So the formula "as is" and as an array seems to work the same. Could you explain the purpose of this?

Last, but not least, I can't thank you enough for this! I spent... I don't know how many, but many hours trying to figure this out.

Thank you, thank you, thank you!!!
1 best response

Accepted Solutions
best response confirmed by JorgeE570 (Copper Contributor)
Solution

@JorgeE570 

In my first reply i didn't realize that in column  [Cut down strings] there are dashes in the end in the first 2 rows: "This is a string -" and "This is another string -". However i'd suggest to use a table without dashes like this: "This is a string" and "This is another string". In the attached file i've changed the formula for column [Result 2] to return the results "1" and "16" and "X - 2".

cut down strings.png

I don't know why it doesn't work in the other file. I'd delete both tables of the other file and start over.

View solution in original post