Look up table, if string ends with digits in table remove last 1 or 2 digits

[ElBB]

Hi all,

I have an excel table that has a large number of data, I wonder if it would be possible to do the following: -

If text in column A ends with specific digits found in another table then remove the last 1 or 2 digits would be removed from column A.
Look up Table 1

LW
RP
Y
N


Table 2

Column A Result column
TestLW Test
Test123 Test123
TestY Test
Test90N Test
TestRP Test
Test1567 Test1567


The above example I have highlighted in bold those that would change and those not in bold would not be affected.

Any help would be appreciated. I know there is find and replace or conditional columns but I may need to quickly add more data to the look up table rather than going into the table to amend the formula or steps in the power query.

 

[AliGW]

Line 4 is anomalous: why not Test90???

 

[AliGW]

Maybe this:

Excel 2016 (Windows) 32 bit

	B​	C​
10​	TestLW	Test
11​	Test123	Test123
12​	TestY	Test
13​	Test90N	Test90
14​	TestRP	Test
15​	Test1567	Test1567

Sheet: Sheet1

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B10,"LW",""),"RP",""),"Y",""),"N","")

 

[ElBB]

My apologies yes it should be Test90

Thanks for the suggestion, I was actually looking for something in power query where it amends based on the look up table look up table which may change.

 

[pinarello]

Here is a fairly simple way to get the desired result.

 

[AliGW]

Here's a one query option.

M Code:

let
    Source = Excel.CurrentWorkbook(){[Name="Tabelle2"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column A", type text}}),
    #"Split Column by Character Transition" = Table.SplitColumn(#"Changed Type", "Column A", Splitter.SplitTextByCharacterTransition({"a".."z"}, {"A".."Z"}), {"Column A.1", "Column A.2"}),
    #"Split Column by Character Transition1" = Table.SplitColumn(#"Split Column by Character Transition", "Column A.1", Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c)), {"Column A.1.1", "Column A.1.2"}),
    #"Removed Columns" = Table.RemoveColumns(#"Split Column by Character Transition1",{"Column A.1.2", "Column A.2"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Column A.1.1", "Result AliGW"}})
in
    #"Renamed Columns"

 

[pinarello]

Hi Ali,

as long as the last character or the last two characters are always uppercase and the text to the left of them doesn't happen to end with an uppercase letter, then of course it's easier that way in any case.

I have now added a few different examples.

But I can remember however a question, which now already somewhat longer past, there I, opposite you, a somewhat more cumbersome solution had provided, only to find out at the end that I had not read the question and the conditions specified therein, correctly.

 

[AliGW]

I NEVER try to guess what the OP hasn't told me - solutions offered are based on the information available and the sample data provided. Your additional examples can only be guestimate of what might be in the real data.