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

E

ElBB

New member
Joined
Apr 30, 2017
Messages
4
Reaction score
0
Points
0
Excel Version(s)
365
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.
 
Maybe this:

Excel 2016 (Windows) 32 bit
B​
C​
10​
TestLWTest
11​
Test123Test123
12​
TestYTest
13​
Test90NTest90
14​
TestRPTest
15​
Test1567Test1567
Sheet: Sheet1

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B10,"LW",""),"RP",""),"Y",""),"N","")
 
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.
 
Here is a fairly simple way to get the desired result.
 

Attachments

  • xlguru - Look up table, if string ends with digits in table remove last 1 or 2 digits (PQ).xlsx
    19.8 KB · Views: 14
Here's a one query option.

M Code:

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"
 

Attachments

  • PQ Look up table, if string ends with digits in table remove last 1 or 2 digits AliGW.xlsx
    22.5 KB · Views: 9
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.
 

Attachments

  • xlguru - Look up table, if string ends with digits in table remove last 1 or 2 digits (PQ).xlsx
    23.6 KB · Views: 10
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.
 
You must log in or register to reply here.
Back
Top