Given alphanumeric strings, insert a dash if two characters are not consecutive in increasing order. Ex. ABY => AB-Y (A & B are consecutive in increasing order and B & Y are not consecutive, hence dash after AB). BA2R => B-A-2-R (B & A are consecutive but not in increasing order. A and 2 are not consecutive. 2 and R are not consecutive)
📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 637
Challenge Difficulty: ⭐️⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn
Solving the challenge of Insert Dashes for Nonconsecutives with Power Query
Power Query solution 1 for Insert Dashes for Nonconsecutives, proposed by Kris Jaganah:
let
A = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
B = Table.AddColumn(
A,
"Answer Expected",
each [
a = Text.ToList([String]),
b = Text.TrimEnd(
Text.Combine(
List.Transform(
List.Positions(a),
each
if
let
d = Character.ToNumber
in
d(a{_ + 1}?) - d(a{_}) = 1
then
a{_}
else
a{_} & "-"
)
),
"-"
)
][b]
)
in
BPower Query solution 2 for Insert Dashes for Nonconsecutives, proposed by Ramiro Ayala Chávez:
let
S = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
T = Table.TransformColumnTypes(S, {"String", type text}),
Fx = (x) =>
let
A = Table.FromColumns({Text.ToList(x)}),
B = Table.Group(A, "Column1", {"G", each [Column1]}, 0),
C = Table.AddIndexColumn(B, "I", 1),
D = Table.AddColumn(C, "C", each Character.ToNumber([Column1]) - [I]),
E = Table.Group(D, "C", {"H", each [G]})[H],
F = List.Transform(E, each List.Transform(_, each Text.Combine(_, "-"))),
G = Text.Combine(List.Transform(F, Text.Combine), "-")
in
G,
Sol = Table.AddColumn(T, "Answer Expected", each Fx([String]))
in
SolPower Query solution 3 for Insert Dashes for Nonconsecutives, proposed by Seokho MOON:
let
Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],
Res = Table.AddColumn(Source, "Answer Expected", F),
F = each [
A = Text.ToList([String]),
B = List.Transform(
List.Zip({A, List.Skip(A)}),
each if Character.ToNumber(_{1}) - Character.ToNumber(_{0}) = 1 then _{1} else "-" & _{1}
),
C = A{0} & Text.Combine(B)
][C]
in
ResSolving the challenge of Insert Dashes for Nonconsecutives with Excel
Excel solution 1 for Insert Dashes for Nonconsecutives, proposed by Bo Rydobon 🇹🇭:
=MAP(A2:A8,LAMBDA(w,LET(v,REGEXEXTRACT(w,".",1),c,CODE(v),TEXTJOIN(REPT("-",ABS(DROP(c,,1)-DROP(c,,-1))<>1),,v))))
=MAP(A2:A8,LAMBDA(w,LET(v,REGEXEXTRACT(w,".",1),c,CODE(v),TEXTJOIN(REPT("-",DROP(c,,1)<>DROP(c,,-1)+1),,v))))Excel solution 2 for Insert Dashes for Nonconsecutives, proposed by Rick Rothstein:
=MAP(
A2:A8,
LAMBDA(
r,
REDUCE(
r,
SEQUENCE(
LEN(
r
)-1,
,
LEN(
r
)-1,
-1
),
LAMBDA(
a,
x,
REPLACE(
a,
x+1,
0,
LEFT(
"-",
1+CODE(
MID(
a,
x,
1
)
)<>CODE(
MID(
a,
x+1,
1
)
)
)
)
)
)
)
)Excel solution 3 for Insert Dashes for Nonconsecutives, proposed by John V.:
=MAP(
A2:A8,
LAMBDA(
x,
LET(
s,
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
),
i,
CODE(
s
),
CONCAT(
s&REPT(
"-",
IFNA(
DROP(
i,
1
)-i,
1
)<>1
)
)
)
)
)Excel solution 4 for Insert Dashes for Nonconsecutives, proposed by 🇰🇷 Taeyong Shin:
=MAP(
A2:A8,
LAMBDA(
x,
LET(
c,
CODE(
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
)
),
TEXTJOIN(
REPT(
"-",
DROP(
c,
-1
)<>DROP(
c-1,
1
)
),
,
CHAR(
c
)
)
)
)
)Excel solution 5 for Insert Dashes for Nonconsecutives, proposed by Kris Jaganah:
=MAP(
A2:A8,
LAMBDA(
x,
LET(
a,
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
),
b,
CODE(
a
),
CONCAT(
a&IF(
VSTACK(
DROP(
b,
1
),
TAKE(
b,
-1
)+1
)-b<>1,
"-",
""
)
)
)
)
)Excel solution 6 for Insert Dashes for Nonconsecutives, proposed by Julian Poeltl:
=MAP(A2:A8,LAMBDA(S,LET(SP,MID(S,SEQUENCE(LEN(S)),1),C,CODE(SP),CONCAT(IF(VSTACK(1,DROP(DROP(C,1)-C,-1))<>1,"-","")&SP))))Excel solution 7 for Insert Dashes for Nonconsecutives, proposed by Hussein SATOUR:
=MAP(A2:A8,LAMBDA(z,CONCAT(SCAN(,MID(z,SEQUENCE(LEN(z),1),1),LAMBDA(x,y,IF(CODE(y)=CODE(RIGHT(TAKE(x,-1)))+1,y,"-"&y))))))Excel solution 8 for Insert Dashes for Nonconsecutives, proposed by Sunny Baggu:
=MAP(
A2:A8,
LAMBDA(
t,
LET(
_m,
MID(
t,
SEQUENCE(
LEN(
t
)
),
1
),
_c,
CODE(
_m
),
_a,
VSTACK(
1,
DROP(
_c,
1
) - DROP(
_c,
-1
)
),
_b,
IF(
_a = 1,
"",
"-"
),
CONCAT(
_b & _m
)
)
)
)Excel solution 9 for Insert Dashes for Nonconsecutives, proposed by LEONARD OCHEA 🇷🇴:
=LET(i,A2:A8,F,LAMBDA(x,MID(i,SEQUENCE(,20,x),1)),LEFT(i)&BYROW(IFERROR(IF(CODE(F(2))-CODE(F(1))=1,"","-")&F(2),""),CONCAT))Excel solution 10 for Insert Dashes for Nonconsecutives, proposed by LEONARD OCHEA 🇷🇴:
=LET(F,LAMBDA(F,s,p,a,LET(i,MID(s,p+1,1),d,a&IF(CODE(MID(s,p,1))=CODE(i)-1,"","-")&i,IF(p+1Excel solution 11 for Insert Dashes for Nonconsecutives, proposed by Md. Zohurul Islam:
=MAP(A2:A8,LAMBDA(x,LET(
a,MID(x,SEQUENCE(LEN(x)),1),
b,CODE(a),
c,DROP(b,1)-DROP(b,-1),
d,IF(c=1,"","-"),
e,IFNA(BYROW(HSTACK(a,d),CONCAT),a),
f,CONCAT(e),
f)))Excel solution 12 for Insert Dashes for Nonconsecutives, proposed by Pieter de B.:
=MAP(
A2:A8,
LAMBDA(
a,
LET(
m,
MID(
a,
SEQUENCE(
LEN(
a
)
),
1
),
CONCAT(
m&IFNA(
IF(
DROP(
CODE(
m
),
1
)-CODE(
m
)=1,
"",
"-"
),
""
)
)
)
)
)Excel solution 13 for Insert Dashes for Nonconsecutives, proposed by Hamidi Hamid:
=LET(
x,
CODE(
MID(
A2:A8,
SEQUENCE(
,
16
),
1
)
),
y,
HSTACK(
DROP(
x,
,
1
),
0
),
t,
IFERROR(
x-y,
""
),
f,
IF(
t=-1,
CHAR(
x
),
IF(
t=0,
CHAR(
x
)&"-",
CHAR(
x
)&"-"
)
),
u,
BYROW(
IFERROR(
f,
""
),
CONCAT
),
LEFT(
u,
LEN(
u
)-1
)
)Excel solution 14 for Insert Dashes for Nonconsecutives, proposed by Asheesh Pahwa:
=LET(s,A2:A8,REDUCE(B1,s,LAMBDA(a,v,VSTACK(a,LET(m,MID(v,SEQUENCE(LEN(v)),1),c,--CODE(m),CONCAT(IF(VSTACK(1,DROP(c,1)-DROP(c,-1))=1,"","-")&m))))))Excel solution 15 for Insert Dashes for Nonconsecutives, proposed by ferhat CK:
=MAP(
A2:A8,
LAMBDA(
x,
LET(
q,
MID(
x,
SEQUENCE(
LEN(
x
)
),
1
),
TAKE(
SCAN(
TAKE(
q,
1
),
DROP(
q,
1
),
LAMBDA(
a,
v,
IF(
CODE(
v
)-CODE(
RIGHT(
a
)
)=1,
a&v,
a&"-"&v
)
)
),
-1
)
)
)
)Excel solution 16 for Insert Dashes for Nonconsecutives, proposed by JvdV -:
=REGEXREPLACE(A2:A8,"(?!"&TEXTJOIN({"","|"},,BASE(ROW(1:36)-{1,0},36))&").B|[9Z]B","$0-")Excel solution 17 for Insert Dashes for Nonconsecutives, proposed by Anup Kumar:
=BYROW(A2:A8,LAMBDA(a,LET(
ar, MID(a,SEQUENCE(,LEN(a)),1),
REDUCE(TAKE(ar,,1),DROP(ar,,1),LAMBDA(x,y,IF(UNICODE(y)-UNICODE(RIGHT(SUBSTITUTE(x,"-",""),1))=1,x&y,x&"-"&y)))
)))Solving the challenge of Insert Dashes for Nonconsecutives with Python
Python solution 1 for Insert Dashes for Nonconsecutives, proposed by Konrad Gryczan, PhD:
import pandas as pd
import numpy as np
path = "637 Insert Dash At Non Consecutive Character.xlsx"
input = pd.read_excel(path, usecols="A", nrows=8)
test = pd.read_excel(path, usecols="B", nrows=8)
def process_string(string):
result = [string[0]]
for i in range(1, len(string)):
if (ord(string[i]) - ord(string[i-1]) != 1):
result.append('-')
result.append(string[i])
return ''.join(result)
input['processed'] = input.iloc[:, 0].apply(process_string)
print(test['Answer Expected'] == input['processed'])
# 0 True
# 1 True
# 2 True
# 3 True
# 4 True
# 5 True
Python solution 2 for Insert Dashes for Nonconsecutives, proposed by Abdallah Ally:
import pandas as pd
def transform_text(text):
chars = [
text[i] if ord(text[i + 1]) - ord(text[i]) == 1 else text[i] + '-'
for i in range(len(text) - 1)
]
return ''.join(chars) + text[-1]
file_path = 'Excel_Challenge_637 - Insert Dash At Non Consecutive Character.xlsx'
df = pd.read_excel(io=file_path)
# Perform data manipulation
df['My Answer'] = df['String'].map(transform_text)
df['Check'] = df['Answer Expected'] == df['My Answer']
df
Solving the challenge of Insert Dashes for Nonconsecutives with Python in Excel
Python in Excel solution 1 for Insert Dashes for Noncons&ecutives, proposed by Alejandro Campos:
def insert_dash_nonconsecutive(s):
return ''.join(a + '-' * (not (a.isdigit() == b.isdigit() and ord(b) == ord(a) + 1)) for a, b in zip(s, s[1:])) + s[-1]
Processed_String = [insert_dash_nonconsecutive(s) for s in xl("A2:A8")[0]]
Python in Excel solution 2 for Insert Dashes for Nonconsecutives, proposed by Seokho MOON:
"Z-Z-Z-1234-4-4-A-A-A-B-B-B" should be " Z-Z-Z-1234-4-4-A-A-AB-B-B"
def dash_at_non_consecutive(txt):
result = [txt[0]]
for prev, curr in zip(txt, txt[1:]):
if ord(curr) - ord(prev) != 1:
result.append("-")
result.append(curr)
return "".join(result)
df["Answer Expected"] = df["String"].apply(dash_at_non_consecutive)
df
Solving the challenge of Insert Dashes for Nonconsecutives with R
R solution 1 for Insert Dashes for Nonconsecutives, proposed by Konrad Gryczan, PhD:
library(tidyverse)
library(readxl)
path = "Excel/637 Insert Dash At Non Consecutive Character.xlsx"
input = read_excel(path, range = "A1:A8")
test = read_excel(path, range = "B1:B8")
process_string = function(string) {
string %>%
str_split("") %>%
unlist() %>%
{tibble(char = ., value = ifelse(is.na(as.numeric(.)), match(., LETTERS), as.numeric(.)))} %>%
mutate(dash = ifelse(value - lag(value) != 1, "-", "")) %>%
replace_na(list(dash = "")) %>%
unite("char", c("dash", "char"), sep = "") %>%
pull(char) %>%
paste0(collapse = "")
}
result = input %>%
mutate(processed = map_chr(String, process_string))
print(result$processed == test$`Answer Expected`)
# [1] TRUE TRUE TRUE TRUE TRUE TRUE FALSE
# one AB shoud be a pair one time in last string
&&