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:(
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:(
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!
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:
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:
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.
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.
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".
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.