Home » Text Split

Text Split

Split the group of texts into single characters Dynamic array function allowed, but extra marks for Legacy solutions or PowerQuery Solution

📌 Challenge Details and Links
Challenge Number: 57
Challenge Difficulty: ⭐
📥Download Sample File
📥Link to the solutions on LinkedIn

Solving the challenge of Text Split with Power Query

Power Query solution 1 for Text Split, proposed by Alejandro Simón 🇵🇦 🇪🇸: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Sol    = List.Combine(Table.TransformColumns(Source, {"Problem", each Text.ToList(_)})[Problem])
in
  Sol
Power Query solution 2 for Text Split, proposed by Abdallah Ally: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Result = Table.FromColumns({Text.ToList(Text.Combine(Source[Problem]))}, {"Solution Required"})
in
  Result
Power Query solution 3 for Text Split, proposed by 🇮🇷 Navid Esmaeilzadeh اسماعیل زاده: 
let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content],  //Table1=B2:B9 & Header="Problem(B1) 
  A      = Table.AddColumn(Source, "L", each Text.ToList([Problem])), 
  C      = List.Combine(A[L])
in
  C
Power Query solution 4 for Text Split, proposed by Krzysztof Kominiak: 
let
  Source = Table.FromList(
    List.Combine(
      Table.TransformColumns(Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], {}, Text.ToList)[
        Problem
      ]
    ), 
    (x) => {x}, 
    {"Answer"}
  )
in
  Source

Solving the challenge of Text Split with Excel

Excel solution 1 for Text Split, proposed by Rick Rothstein: 
=MID(
   CONCAT(
       B3:B9),
   SEQUENCE(
       SUM(
           LEN(
       B3:B9))),
   1)
Excel solution 2 for Text Split, proposed by Rick Rothstein: 
=MID(
   B$3&B$4&B$5&B$6&B$7&B$8&B$9,
   ROWS(
       $1:1),
   1)
Excel solution 3 for Text Split, proposed by Julian Poeltl: 
=LET(
   C,
   CONCAT(
       B3:B9),
   MID(
       C,
       SEQUENCE(
           LEN(
               C)),
       1))
Excel solution 4 for Text Split, proposed by Oscar Mendez Roca Farell: 
=LET(
   m,
   MID(
       B3:B9,
       COLUMN(
           A:D),
       1),
   TOCOL(
       IFS(
           m>"",
           m),
       2))
Excel solution 5 for Text Split, proposed by Duy Tùng: 
=TOCOL(
   REGEXEXTRACT(
       CONCAT(
           B3:B9),
       ".",
       1))
Excel solution 6 for Text Split, proposed by Sunny Baggu: 
=LET(
   
    _a,
    CONCAT(
        B3:B9),
   
    MID(
        _a,
         SEQUENCE(
             LEN(
                 _a)),
         1)
   )
Excel solution 7 for Text Split, proposed by Abdallah Ally: 
=LET(
   a,
   CONCAT(
       B3:B9),
   MID(
       a,
       SEQUENCE(
           LEN(
               a)),
       1))
Excel solution 8 for Text Split, proposed by Md. Zohurul Islam: 
=DROP(
   REDUCE(
       "",
       B3:B9,
       LAMBDA(
           x,
           y,
           VSTACK(
               x,
               MID(
                   y,
                   SEQUENCE(
                       LEN(
                           y)),
                   1)))),
   1)
Excel solution 9 for Text Split, proposed by Pieter de B.: 
=LET(
   a,
   B3:B9,
   s,
   SEQUENCE,
   l,
   LEN(
       a),
   m,
   s(
       ,
       MAX(
           l)),
   TOCOL(
       MID(
           a,
           IFS(
               l>=m,
               m),
           1),
       2))

Or
=LET(
   s,
   SEQUENCE(
       ,
       9),
   TOCOL(
       MID(
           B3:B9,
           IFS(
               LEN(
                   B3:B9)>=s,
               s),
           1),
       2))
Excel solution 10 for Text Split, proposed by Hamidi Hamid: 
=MID(
   CONCAT(
       B3:B9),
   SEQUENCE(
       LEN(
           CONCAT(
       B3:B9))),
   1)
Excel solution 11 for Text Split, proposed by Asheesh Pahwa: 
=DROP(
   REDUCE(
       "",
       B3:B9,
       LAMBDA(
           x,
           y,
           VSTACK(
               x,
               
               MID(
                   y,
                   SEQUENCE(
                       LEN(
                           y)),
                   1)))),
   1)

=LET(
   c,
   CONCAT(
       B3:B9),
   
   MID(
       c,
       SEQUENCE(
           LEN(
               c)),
       1))
Excel solution 12 for Text Split, proposed by Victor Momoh (MVP, MOS, R.Eng): 
=TOCOL(
   REGEXEXTRACT(
       CONCAT(
           B3:B9),
       "[a-z]",
       1,
       1))
Excel solution 13 for Text Split, proposed by Ernesto Vega Castillo: 
=LET(
   x,
   TEXTJOIN(
       "",
       1,
       B3:B9),
   MID(
       x,
       SEQUENCE(
           LEN(
               x)),
       1))
Excel solution 14 for Text Split, proposed by Tomasz Jakóbczyk: 
=MID(
   CONCAT(
       B3:B9),
   SEQUENCE(
       LEN(
           CONCAT(
       B3:B9))),
   1)
Excel solution 15 for Text Split, proposed by Md. Shah Alam, Microsoft Certified Trainer: 
=MID(
   CONCAT(
       B3:B9),
   SEQUENCE(
       LEN(
           CONCAT(
       B3:B9))),
   1)
Excel solution 16 for Text Split, proposed by Mohit Rawat: 
=MID(
   CONCAT(
       B3:B9),
   SEQUENCE(
       LEN(
           CONCAT(
       B3:B9))),
   1)
Excel solution 17 for Text Split, proposed by Enrico Mendiola: 
=MID(
   TEXTJOIN(
       "",
       TRUE,
       B3:B9),
   SEQUENCE(
       LEN(
           TEXTJOIN(
               "",
               TRUE,
               B3:B9))),
   1)
Excel solution 18 for Text Split, proposed by Claire Squibb: 
=LET(
   txt,
   CONCAT(
       B3:B9),
   MID(
       txt,
       SEQUENCE(
           LEN(
               txt)),
       1))
Excel solution 19 for Text Split, proposed by abdelaziz allam: 
=LET(
   x,
   TEXTJOIN(
       "",
       TRUE,
       P9:P15),
   MID(
       x,
       SEQUENCE(
           LEN(
               x)),
       1))

Solving the challenge of Text Split with Python in Excel

Python in Excel solution 1 for Text Split, proposed by Alejandro Campos: 
One 
characters = [char for text in xl("B3:B9")[0] for char in text]
Python in Excel solution 2 for Text Split, proposed by Mey Tithveasna: 
Abdallah Ally 
PY()
df = xl("B3:B9", headers=True)
joinstring= ''.join(df[0])
result=list(joinstring)
# print result
result

Solving the challenge of Text Split with R

R solution 1 for Text Split, proposed by Konrad Gryczan, PhD: 
library(tidyverse)
library(readxl)
path = "files/Excel Challenge 29th Dec.xlsx"
input = read_excel(path, range = "B2:B9")
test = read_excel(path, range = "D2:D15")
result = input %>%
 separate_rows(Problem, sep = "") %>%
 filter(Problem != "")
all.equal(result, test, check.attributes = FALSE)
#> [1] TRUE
R solution 2 for Text Split, proposed by Konrad Gryczan, PhD: 
library(tidyverse)
library(readxl)
path = "files/Excel Challenge 29th Dec.xlsx"
input = read_excel(path, range = "B2:B9")
test = read_excel(path, range = "D2:D15")
result = input %>%
 separate_rows(Problem, sep = "") %>%
 filter(Problem != "")
all.equal(result, test, check.attributes = FALSE)
#> [1] TRUE

Leave a Reply