Home » Extract Numbers After The Last alphabet

Extract Numbers After The Last alphabet

Extract the LAST Correct Order These are numbers/symbols following the last alphabet letter. Dynamic array function allowed, but Extra marks for Legacy solutions or PowerQuery Solution Check the comment to download the workbook.

📌 Challenge Details and Links
Challenge Number: 30
Challenge Difficulty: ⭐⭐
📥Link to the solutions on LinkedIn

Solving the challenge of Extract Numbers After The Last alphabet with Power Query

Power Query solution 1 for Extract Numbers After The Last alphabet, proposed by Aditya Kumar Darak 🇮🇳: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Return = Table.AddColumn(
    Source, 
    "Last Correct Order", 
    each [
      S = Splitter.SplitTextByCharacterTransition({"A" .. "Z", "a" .. "z"}, {"#", "0" .. "9"})(
        [#"Customers & Orders"]
      ), 
      R = List.Select(S, (f) => f = Text.Select(f, {"#", "0" .. "9"})){0}?
    ][R]
  )
in
  Return
Power Query solution 2 for Extract Numbers After The Last alphabet, proposed by Alejandro Simón 🇵🇦 🇪🇸: 
let
 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 Sol = Table.AddColumn(Source, "Last Correct Order", each 
 if List.AnyTrue(List.Transform({"rejected", "not yet confirmed", "to confirm"}, 
 (x)=> Text.Contains (Text.Lower([#"Customers & Orders"]), x))) then null else 
 List.Last(List.Select(Text.SplitAny(Text.Lower([#"Customers & Orders"]), Text.Combine({"a".."z"})), each _<>"")))
in
 Sol
No me hace mucho sentido que, por ejemplo, la última fila, "Orders #564 and #5862 were rejected", lo que haga que se excluya sea los espacios anteriores al #, cuando realmente lo que indica que no es una orden correcta es la palabra "rejected", y si dijera, en lugar de "rejected", "correct"??
Power Query solution 3 for Extract Numbers After The Last alphabet, proposed by Brian Julius: 
let
  Source = Table1, 
  Rscript = Table.SelectRows(
    Table.ExpandTableColumn(
      Table.RemoveColumns(
        R.Execute(
          "library(stringr)#(lf)df <- dataset#(lf)df$Answer <- str_match(df$CustomerOrders, ""(?<=[a-zA-Z])([#0-9-]+)$"")#(lf)df", 
          [dataset = Source]
        ), 
        "Name"
      ), 
      "Value", 
      {"CustomerOrders", "Answer"}, 
      {"Customers & Orders", "Last Correct Order"}
    ), 
    each [#"Customers & Orders"] <> null
  )
in
  Rscript

Solving the challenge of Extract Numbers After The Last alphabet with Excel

Excel solution 1 for Extract Numbers After The Last alphabet, proposed by Bo Rydobon 🇹🇭: 
=IFNA(
   REGEXEXTRACT(
       C3:C8,
       "#?d+$"),
   "")
Excel solution 2 for Extract Numbers After The Last alphabet, proposed by محمد حلمي: 
=IFNA(
   -LOOKUP(
       0,
       -RIGHT(
           C3,
           ROW(
               $1:$9))),
   "")
Excel solution 3 for Extract Numbers After The Last alphabet, proposed by Julian Poeltl: 
=MAP(
   C3:C8,
   LAMBDA(
       C,
       TAKE(
           TEXTSPLIT(
               C,
               CHAR(
                   64+SEQUENCE(
                       26)),
               ,
               ,
               1),
           ,
           -1)))
Excel solution 4 for Extract Numbers After The Last alphabet, proposed by Hussein SATOUR: 
=IFNA(
   REGEXEXTRACT(
       C3:C8,
       "[^a-zA-Z]+$"),
   "")
Excel solution 5 for Extract Numbers After The Last alphabet, proposed by Sunny Baggu: 
=MAP(
   
    C3:C8,
   
    LAMBDA(
        x,
        
         TAKE(
             TEXTSPLIT(
                 UPPER(
                     x),
                  ,
                  CHAR(
                      SEQUENCE(
                          26,
                           ,
                           CODE(
                               "A"))),
                  1),
              -1)
         )
   )
Excel solution 6 for Extract Numbers After The Last alphabet, proposed by 🇵🇪 Ned Navarrete C.: 
=MAP(
   C3:C8,
   LAMBDA(
       f,
       TEXTAFTER(
           f,
           TAKE(
               TOCOL(
                   TEXTSPLIT(
                       f,
                       ROW(
                           1:10)-1,
                       "#",
                       1)),
               -1))))
Excel solution 7 for Extract Numbers After The Last alphabet, proposed by Meganathan Elumalai: 
=MID(
   $B4,
   MAX(
       ISERROR(
           MID(
               $B4,
               ROW(
                   INDIRECT(
                       "1:"&LEN(
                           $B4))),
               1)*1)*ROW(
           INDIRECT(
               "1:"&LEN(
                           $B4))))+1,
   25)
Excel solution 8 for Extract Numbers After The Last alphabet, proposed by JvdV –: 
=REGEXEXTRACT(
   C3:C8,
   ".*[a-z](#?d+)",
   2)
Excel solution 9 for Extract Numbers After The Last alphabet, proposed by Milan Shrimali: 
=MAP(
   A2:A4,
   
   LAMBDA(
       x,
       TAKE(
           
           TEXTSPLIT(
               x,
               CHAR(
                   VSTACK(
                       SEQUENCE(
                           26,
                           ,
                           97),
                       SEQUENCE(
                           26,
                           ,
                           65)))),
           ,
           -1)))
Excel solution 10 for Extract Numbers After The Last alphabet, proposed by Shimi Naim: 
IFERROR(
   --TEXTSPLIT(
       TEXTAFTER(
           A2:A7),
       "(",
       SEQUENCE(
           ,
           3)),
   ")"),
   "")
Excel solution 11 for Extract Numbers After The Last alphabet, proposed by Farzad Heidary: 
MAP(
   c3:c8,
   LAMBDA(
       x,
       TAKE(
           TEXTSPLIT(
               x,
               CHAR(
                   SEQUENCE(
                       26,
                       ,
                       65)),
               ,
               1,
               1),
           ,
           -1)))

Solving the challenge of Extract Numbers After The Last alphabet with Python

Python solution 1 for Extract Numbers After The Last alphabet, proposed by Konrad Gryczan, PhD: 
Validation "by eye".
import pandas as pd
import re
import numpy as np
input = pd.read_excel('files/Excel Challenge 2nd June.xlsx', usecols='C', skiprows=1, nrows = 6)
test  = pd.read_excel('files/Excel Challenge 2nd June.xlsx', usecols='D', skiprows=1, nrows = 6)
test['Last Correct Order'] = test['Last Correct Order'].replace(np.nan, '', regex=True)
def extract_after_last_letter(s):
 match = list(re.finditer(r'[A-Za-z]', s))
 if match:
 last_letter_pos = match[-1].end()
 return s[last_letter_pos:] if last_letter_pos < len(s) else ''
 return ''
input['answer'] = input['Customers & Orders'].apply(extract_after_last_letter)
print(input.merge(test, left_index=True, right_index=True))

Solving the challenge of Extract Numbers After The Last alphabet with Python in Excel

Python in Excel solution 1 for Extract Numbers After The Last alphabet, proposed by Abdallah Ally: 
import pandas as pd
import re
file_path = 'Easy Excel Challenge 2nd June.xlsx'
df = pd.read_excel(file_path, usecols='C:D', skiprows=1, nrows=6)
df = df.replace(float('nan'), '')
# Perform data wrangling
def extract_last_order(text):
 matches = re.findall('[a-zA-Z](#?d+)', text)
 return matches[-1] if matches else ''
 
df['My Last Correct Order'] = df.iloc[:, 0].map(extract_last_order)
df

Solving the challenge of Extract Numbers After The Last alphabet with R

R solution 1 for Extract Numbers After The Last alphabet, proposed by Konrad Gryczan, PhD: 
library(tidyverse)
library(readxl)
input = read_excel("files/Excel Challenge 2nd June.xlsx", range = "C2:C8")
test = read_excel("files/Excel Challenge 2nd June.xlsx", range = "D2:D8")
result = input %>%
 mutate(last_letter = map_int(str_locate_all(`Customers & Orders`, "[A-Za-z]"), ~max(.x[,2]))) %>%
 mutate(answer = ifelse(last_letter < nchar(`Customers & Orders`), str_sub(`Customers & Orders`, last_letter + 1), NA_character_))
 
identical(result$answer, test$`Last Correct Order`) 
#> [1] TRUE

Leave a Reply