Extract special characters from given strings.
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 456
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Extract Special Characters Only with Power Query
Power Query solution 1 for Extract Special Characters Only, proposed by Aditya Kumar Darak 🇮🇳:
let
Source = Excel.CurrentWorkbook(){[Name = "data"]}[Content],
Return = Table.AddColumn(
Source,
"Answer",
each Text.Remove([String], {"0" .. "9", "A" .. "Z", "a" .. "z"})
)
in
ReturnPower Query solution 2 for Extract Special Characters Only, proposed by Alejandro Simón 🇵🇦 🇪🇸:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.AddColumn(
Source,
"Answer",
each Text.Remove([String], {"A" .. "Z", "a" .. "z", "0" .. "9"})
)
in
SolPower Query solution 3 for Extract Special Characters Only, proposed by Luan Rodrigues:
let
Fonte = Tabela1,
res = Table.AddColumn(
Fonte,
"Personalizar",
each Text.Combine(
List.Difference(
Text.ToList([String]),
Text.ToList(Text.Select([String], {"a" .. "z", "0" .. "9", "A" .. "Z"}))
)
)
)
in
resPower Query solution 4 for Extract Special Characters Only, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Sol = Table.AddColumn(
S,
"Expected Answer",
each Text.Select([String], {"%", "&", "@", ".", "*", "^", "#", "$", "(", ")", "+", "~", " "})
)
in
SolPower Query solution 5 for Extract Special Characters Only, proposed by Cristobal Salcedo Beltran:
let
Source = Excel.CurrentWorkbook(){0}[Content],
Result = Table.AddColumn(
Source,
"Expected Answer",
each Text.Select([String], {"#" .. "+", "~", ".", "^", "@"})
)
in
ResultPower Query solution 6 for Extract Special Characters Only, proposed by rhonal chairul:
= Table.AddColumn(#"Changed Type", "Answer", each Text.Remove ([Column1], {"A".."Z","a".."z","0".."9"}))Power Query solution 7 for Extract Special Characters Only, proposed by Cristobal Salcedo Beltran:
let
Source = Excel.CurrentWorkbook(){0}[Content],
Result = Table.AddColumn(
Source,
"Expected Answer",
each Text.Remove([String], {"0" .. "9", "a" .. "z", "A" .. "Z"})
)
in
ResultSolving the challenge of Extract Special Characters Only with Excel
Excel solution 1 for Extract Special Characters Only, proposed by Bo Rydobon 🇹🇭:
Ashutosh Sharma fixed
SEQUENCE(
26,
,
65
) start from 65Excel solution 2 for Extract Special Characters Only, proposed by Bo Rydobon 🇹🇭:
=MAP(
A2:A10,
LAMBDA(
a,
CONCAT(
TEXTSPLIT(
a,
SEQUENCE(
10
)-1,
CHAR(
SEQUENCE(
26
)+64
),
,
1,
""
)
)
)
)Excel solution 3 for Extract Special Characters Only, proposed by Bo Rydobon 🇹🇭:
=MAP(
A2:A10,
LAMBDA(
a,
LET(
m,
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
),
CONCAT(
REPT(
m,
m<"0"
)
)
)
)
)Excel solution 4 for Extract Special Characters Only, proposed by Rick Rothstein:
=MAP(
A2:A10,
LAMBDA(
x,
TEXTJOIN(
"",
,
TEXTSPLIT(
UPPER(
x
),
BASE(
SEQUENCE(
36,
,
0
),
36
)
)
)
)
)Excel solution 5 for Extract Special Characters Only, proposed by Rick Rothstein:
=IFERROR(
MAP(
A2:A10,
LAMBDA(
x,
CONCAT(
TEXTSPLIT(
UPPER(
x
),
BASE(
SEQUENCE(
36,
,
0
),
36
)
)
)
)
),
""
)Excel solution 6 for Extract Special Characters Only, proposed by John V.:
=MAP(
A2:A10,
LAMBDA(
x,
CONCAT(
TEXTSPLIT(
x,
BASE(
ROW(
1:36
)-1,
36
),
,
,
1
)
)
)
)Excel solution 7 for Extract Special Characters Only, proposed by محمد حلمي:
=MAP(
A2:A10,
LAMBDA(
a,
LET(
d,
CHAR(
SEQUENCE(
255
)
),
CONCAT(
TEXTSPLIT(
a,
IF(
d>="0",
d
)
)
)
)
)
)Excel solution 8 for Extract Special Characters Only, proposed by Kris Jaganah:
=REDUCE(
A2:A10,
LET(
a,
SEQUENCE(
26,
,
0
),
VSTACK(
CHAR(
a+97
),
CHAR(
a+65
),
a
)
),
LAMBDA(
x,
y,
SUBSTITUTE(
x,
y,
""
)
)
)Excel solution 9 for Extract Special Characters Only, proposed by Kris Jaganah:
=REDUCE(
A2:A10,
LET(
a,
SEQUENCE(
26,
,
0
),
HSTACK(
a,
CHAR(
a+{65,
97}
)
)
),
LAMBDA(
x,
y,
SUBSTITUTE(
x,
y,
""
)
)
)Excel solution 10 for Extract Special Characters Only, proposed by Julian Poeltl:
=MAP(
A2:A10,
LAMBDA(
S,
LET(
SP,
MID(
S,
SEQUENCE(
LEN(
S
)
),
1
),
R,
TEXTSPLIT(
SP,
CHAR(
VSTACK(
SEQUENCE(
10,
,
CODE(
0
)
),
SEQUENCE(
26,
,
CODE(
"a"
)
)
)
),
,
,
1
),
CONCAT(
R
)
)
)
)Excel solution 11 for Extract Special Characters Only, proposed by Julian Poeltl:
=MAP(
A2:A10,
LAMBDA(
S,
LET(
SP,
MID(
S,
SEQUENCE(
LEN(
S
)
),
1
),
C,
CODE(
SP
),
R,
MAP(
C,
LAMBDA(
A,
OR(
A=SEQUENCE(
10,
,
CODE(
0
)
),
A=SEQUENCE(
26,
,
CODE(
"A"
)
),
A=SEQUENCE(
26,
,
CODE(
"a"
)
)
)
)
),
IFERROR(
CONCAT(
FILTER(
SP,
R=FALSE
)
),
""
)
)
)
)Excel solution 12 for Extract Special Characters Only, proposed by Timothée BLIOT:
=MAP(
A2:A10,
LAMBDA(
x,
IFNA(
CONCAT(
REGEXEXTRACT(
x,
"[^w]",
1
)
),
""
)
)
)Excel solution 13 for Extract Special Characters Only, proposed by Nikola Z Grujicic - Nikola Ž Grujičić:
=IFERROR(
MAP(
A2:A10,
LAMBDA(
a,
LET(
d,
MID(
a,
SEQUENCE(
LEN(
a
),
,
,
1
),
1
),
e,
CODE(
LOWER(
d
)
)=CODE(
d
),
f,
CODE(
UPPER(
d
)
)=CODE(
d
),
g,
NOT(
ISNUMBER(
--d
)
),
h,
e*f*g,
TEXTJOIN(
"",
TRUE,
FILTER(
d,
h=1
)
)
)
)
),
""
)Excel solution 14 for Extract Special Characters Only, proposed by 🇵🇪 Ned Navarrete C.:
=MAP(
A2:A10,
LAMBDA(
f,
LET(
c,
MID(
f,
ROW(
1:11
),
1
),
CONCAT(
FILTER(
c,
ISNA(
XMATCH(
c,
BASE(
SEQUENCE(
36
)-1,
36
)
)
),
""
)
)
)
)
)Excel solution 15 for Extract Special Characters Only, proposed by Asheesh Pahwa:
=LET(
st,
A2:A10,
alp,
CHAR(
SEQUENCE(
26
)+96
),
nm,
SEQUENCE(
10,
,
0
),
DROP(
REDUCE(
"",
st,
LAMBDA(
a,
v,
VSTACK(
a,
LET(
t,
TEXTSPLIT(
LOWER(
v
),
alp,
nm
),
CONCAT(
IFNA(
t,
""
)
)
)
)
)
),
1
)
)Excel solution 16 for Extract Special Characters Only, proposed by Andy Heybruch:
= value or range;
[_Special] = 1 to remove special char
[_Numbers] = 1 to remove numbers
[_Letters] = 1 to remove letters
[_Spaces] = 1 to remover spaces
=LAMBDA(_input,
[_Special],
[_Numbers],
[_Letters],
[_Spaces],
MAP(
_input,
LAMBDA(_string,
LET(
_array,
MID(
_string,
SEQUENCE(
LEN(
_string
)
),
1
),
_L,
(CODE(
UPPER(
_array
)
) >= 65) * (CODE(
UPPER(
_array
)
) <= 90),
_N,
--(ISNUMBER(
--_array
)),
_SP,
--(_array = " "),
_S,
(_L = 0) * (_N = 0) * (_SP = 0),
_check,
(_Special * _S) + (_Numbers * _N) + (_Letters * _L) + (_Spaces * _SP),
IFERROR(
CONCAT(
FILTER(
_array,
_check = 0
)
),
""
)
)
)
)
)Excel solution 17 for Extract Special Characters Only, proposed by Milan Shrimali:
=MAP(
A1:A10,
LAMBDA(
y,
LET(
a,
MID(
y,
SEQUENCE(
LEN(
y
)
),
1
),
stck,
VSTACK(
CHAR(
SEQUENCE(
26,
,
65
)
),
CHAR(
SEQUENCE(
26,
1,
97,
1
)
),
CHAR(
SEQUENCE(
10,
,
48,
1
)
)
),
TEXTJOIN(
",",
,
IF(
ISNA(
MATCH(
a,
stck,
0
)
),
a,
""
)
)
)
)
)Excel solution 18 for Extract Special Characters Only, proposed by Nicolas Micot:
=LET(
_string;
MINUSCULE(
A2
);
_blacklist;
"abcdefghijklmnopqrstuvwxyz0123456789";
REDUCE(
_string;
STXT(
_blacklist;
SEQUENCE(
NBCAR(
_blacklist
)
);
1
);
LAMBDA(
l_string;
l_blacklist;
SUBSTITUE(
l_string;
l_blacklist;
""
)
)
)
)Excel solution 19 for Extract Special Characters Only, proposed by Ziad A.:
=REGEXREPLACE(
A2,
"[A-Za-z0-9]",
)Excel solution 20 for Extract Special Characters Only, proposed by Ziad A.:
=JOIN(,SPLIT(UPPER(A2),SORT(JOIN(,ROW(1:10),CHAR(ROW(65:90))),1,)))Excel solution 21 for Extract Special Characters Only, proposed by Iván Cortinas Rodríguez:
=REDUCE(
A2:A10,
TOCOL(
HSTACK(
CHAR(
SEQUENCE(
26,
,
65
)
),
CHAR(
SEQUENCE(
26,
,
97
)
),
CHAR(
SEQUENCE(
10,
,
48
)
)
),
2
),
LAMBDA(
a,
v,
SUBSTITUTE(
a,
v,
""
)
)
)Excel solution 22 for Extract Special Characters Only, proposed by Ernesto Vega Castillo:
=MAP(
A2:A10,
LAMBDA(
a,
IFERROR(
CONCAT(
TEXTSPLIT(
a,
VSTACK(
CHAR(
SEQUENCE(
10,
,
CODE(
0
)
)
),
CHAR(
SEQUENCE(
26,
,
CODE(
"a"
)
)
)
),
,
,
1
)
),
""
)
)
)Excel solution 23 for Extract Special Characters Only, proposed by Ernesto Vega Castillo:
=BYROW(
A2:A10,
LAMBDA(
a,
IFERROR(
CONCAT(
TEXTSPLIT(
a,
VSTACK(
CHAR(
SEQUENCE(
10,
,
48
)
),
CHAR(
SEQUENCE(
26,
,
65
)
),
CHAR(
SEQUENCE(
26,
,
97
)
)
),
,
1
)
),
""
)
)
)Excel solution 24 for Extract Special Characters Only, proposed by Murat Bayraktar:
=MAP(
A2:A10;
LAMBDA(
p;
LET(
a;
SEQUENCE(
26;
;
97
);
b;
SEQUENCE(
26;
;
65
);
c;
SEQUENCE(
10;
;
48
);
CONCAT(
TEXTSPLIT(
p;
UNICHAR(
VSTACK(
a;
b;
c
)
)
)
)
)
)
)Excel solution 25 for Extract Special Characters Only, proposed by Shyamsundar Baggu:
=MAP(
A2:A10,
LAMBDA(
a,
CONCAT(
TEXTSPLIT(
a,
SEQUENCE(
10
)-1,
CHAR(
SEQUENCE(
26,
,
CODE(
"A"
)
)
),
,
1,
""
)
)
)
)Excel solution 26 for Extract Special Characters Only, proposed by Shyamsundar Baggu:
=MAP(
A2:A10,
LAMBDA(
t,
LET(
a,
""&VSTACK(
SEQUENCE(
10
)-1,
CHAR(
SEQUENCE(
26,
,
CODE(
"A"
)
)
)
),
b,
UPPER(
MID(
t,
SEQUENCE(
,
LEN(
t
)
),
1
)
),
CONCAT(
FILTER(
b,
BYCOL(
b=a,
LAMBDA(
d,
1-OR(
d
)
)
),
""
)
)
)
)
)Excel solution 27 for Extract Special Characters Only, proposed by Nikki Prajapati:
=TEXTJOIN(
"",
TRUE,
IF(
ISERR(
FIND(
MID(
A1,
ROW(
INDIRECT(
"1:" & LEN(
A1
)
)
),
1
),
"ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789"
)
),
MID(
A1,
ROW(
INDIRECT(
"1:" & LEN(
A1
)
)
),
1
),
""
)
)Excel solution 28 for Extract Special Characters Only, proposed by Ashutosh Sharma:
=IFERROR(
CONCAT(
FILTER(
MID(
A2,
SEQUENCE(
LEN(
A2
)
),
1
),
ISNA(
XMATCH(
MID(
A2,
SEQUENCE(
LEN(
A2
)
),
1
),
VSTACK(
CHAR(
SEQUENCE(
26,
1,
65
)
),
CHAR(
SEQUENCE(
26,
1,
97
)
),
TEXT(
SEQUENCE(
10,
1,
0
),
0
)
)
)
)
)
),
""
)Solving the challenge of Extract Special Characters Only with Python
Python solution 1 for Extract Special Characters Only, proposed by Konrad Gryczan, PhD:
import pandas as pd
import numpy as np
input = pd.read_excel('456 Extract special Characters.xlsx', usecols="A", nrows = 10)
test = pd.read_excel('456 Extract special Characters.xlsx', usecols="B", nrows = 10)
# Approach 1 - remove alphanumeric characters
result = input['String'].str.replace(r'w+', '', regex=True)
result = result.replace(r'^s*$', np.nan, regex=True)
print(result.equals(test['Expected Answer'])) # True
# Approach 2 - extract special characters
result2 = input['String'].str.extractall(r'([^a-zA-Z0-9])')[0].unstack()
result2 = result.replace(r'^s*$', np.nan, regex=True)
print(result2.equals(test['Expected Answer'])) # True
Python solution 2 for Extract Special Characters Only, proposed by Owen Price:
Solving the challenge of Extract Special Characters Only with Python in Excel
Python in Excel solution 1 for Extract Special Characters Only, proposed by Abdallah Ally:
import pandas as pd
import re
def extract_special_Characters(col):
return re.sub('[^_W]', '', col) # => [a-zA-Z0-9]
file_path = 'Excel_Challenge_456 -Extract special Characters.xlsx'
df = pd.read_excel(file_path).replace(float('nan'), '')
# Perform data transformation and cleansing
df['My Answer'] = df['String'].apply(extract_special_Characters)
df['Check'] = df['Expected Answer'] == df['My Answer']
# Display results
df
Python in Excel solution 2 for Extract Special Characters Only, proposed by JvdV -:
Some options:
=PY(xl("A2:A10").replace('[^W_]','',regex=True).values)
=LET(s,MID(A2:A10,SEQUENCE(,99),1),BYROW(REPT(s,s<"0"),CONCAT))
=MAP(A2:A10,LAMBDA(s,CONCAT(TEXTSPLIT(s,BASE(SEQUENCE(36)-1,36),,,1))))
=REDUCE(UPPER(A2:A10),BASE(SEQUENCE(36)-1,36),LAMBDA(x,y,SUBSTITUTE(x,y;,)))
Solving the challenge of Extract Special Characters Only with R
R solution 1 for Extract Special Characters Only, proposed by Ahmad Algadeeb, MBA:
# Load libraries
library(readxl)
library(janitor)
library(tidyverse)
# load data ---------------------------------------------------------------
df <- read_excel("/Downloads/Excel_Challenge_456 -Extract special Characters.xlsx") |>
clean_names()
# transformations ---------------------------------------------------------
df_tbl <- df |>
mutate(
# extract pattern
expected_answer = str_extract_all(string, "[^aA-zZ|0-9]|\^") |>
# flatten up list output
map_chr(~paste(.x, collapse = "")) %>%
ifelse(.=="",NA, .))
identical(df, df_tbl)
, )
Solving the challenge of Extract Special Characters Only with Excel VBA
Excel VBA solution 1 for Extract Special Characters Only, proposed by Nikki Prajapati:
Function RemoveAlphaNumeric(text As String) As String
Dim result As String
Dim i As Integer
result = ""
For i = 1 To Len(text)
Dim char As String
char = Mid(text, i, 1)
If Not char Like "[A-Za-z0-9]" Then
result = result & char
End If
Next i
RemoveAlphaNumeric = result
End Function
&&