Find match in string

1

Hi

need help with finding partial string in string.
I have this two dummy tables:

In Table 1 are just master data, where i from column 2 getting part of the string into the column 3 with formula Left() also i added a Totext() formula.

In the Table 2 in column 1 i have select list which makes a unique list from Table 1.column 1. The column 2 is filtering the Table 1 by the column 1 from Table 2 and lookups in the column 4 for the string i need “B1B” and sums.

But lets say i want to find just “B1” in that string from Table 1 and sum the column 4 and that result write in to the Table 2.

I know i can make a new column with formula "Left(column2, 2) and i get the result, but wanted to know if it is possible without adding new column and doing it in one step/formula.

Hope this makes sense

Thanks
Tomas

2

Hi @Tomas_Sebes ,
I provided an implementation for your use case directly in your document: let me know if it helps.

Briefly:
You can take advantage of RegexMatch() formula.

Also:
Lookup() is basically a shortcut for Filter(), therefore your formula

Filter([Table 1],[Column 1]=thisRow.[Column 1]).Lookup([Column 3], "B1B").[Column 4].Sum()

can be simplified with:

[Table 1].Filter([Column 1]=thisRow.[Column 1] AND [Column 3]="B1B").[Column 4].Sum()

I hope this helps.
Cheers!

2 Likes
3

Thanks a lot, i knew i could somehow use the regexmatch() but could not figure it how.

1 Like