Add Spaces Between Types with Excel Formula

Insert a space whenever there is a transition from alphabet to number OR number to alphabet in the strings. Hence if string is “wer12ty”, then space will entered between r and 1 & 2 and t. Hence answer will be “wer 12 ty”. For row 7, underscore in the answer is by mistake. It is space only. Corrected in the Excel.

📌 Challenge Details and Links
ExcelBI Excel Challenge Number: 128
Challenge Difficulty: ⭐️
📥Download Sample File
📥Link to the solutions on LinkedIn

Solving the challenge of Add Spaces Between Types with Power Query

Power Query solution 1 for Add Spaces Between Types, proposed by Bo Rydobon 🇹🇭:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Ans = Table.TransformRows(
    Source, 
    each 
      let
        m = Text.ToList([String]), 
        s = List.Transform(m, each Value.Is(try Number.From(_) otherwise "A", type number))
      in
        Text.Trim(
          Text.Combine(
            List.Transform(List.Positions(m), each m{_} & (if s{_} <> s{_ + 1}? then " " else ""))
          )
        )
  )
in
  Ans

Power Query solution 2 for Add Spaces Between Types, proposed by Brian Julius:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  DupeCol = Table.DuplicateColumn(Source, "String", "String2"), 
  SplitNumToChar = Table.TransformColumns(
    DupeCol, 
    {
      {
        "String2", 
        Splitter.SplitTextByCharacterTransition(
          {"0" .. "9"}, 
          (c) => not List.Contains({"0" .. "9"}, c)
        ), 
        let
          itemType = (type nullable text) meta [Serialized.Text = true]
        in
          type {itemType}
      }
    }
  ), 
  Expand = Table.ExpandListColumn(SplitNumToChar, "String2"), 
  SplitCharToNum = Table.TransformColumns(
    Expand, 
    {
      {
        "String2", 
        Splitter.SplitTextByCharacterTransition(
          (c) => not List.Contains({"0" .. "9"}, c), 
          {"0" .. "9"}
        ), 
        let
          itemType = (type nullable text) meta [Serialized.Text = true]
        in
          type {itemType}
      }
    }
  ), 
  Expande2 = Table.ExpandListColumn(SplitCharToNum, "String2"), 
  Group = Table.Group(
    Expande2, 
    {"String"}, 
    {{"All", each _, type table [String = text, String2 = nullable text]}}
  ), 
  #"Added Custom" = Table.RemoveColumns(
    Table.AddColumn(Group, "Answer", each Text.Combine([All][String2], " ")), 
    "All"
  )
in
  #"Added Custom"

Power Query solution 3 for Add Spaces Between Types, proposed by Bhavya Gupta:

let
  Source = Excel.CurrentWorkbook(){[Name = "Table1"]}[Content], 
  Final = Table.AddColumn(
    Source, 
    "Result", 
    each Text.Combine(
      List.TransformMany(
        Splitter.SplitTextByCharacterTransition({"a" .. "z"}, {"0" .. "9"})(Text.From([String])), 
        (a) => Splitter.SplitTextByCharacterTransition({"0" .. "9"}, {"a" .. "z"})(a), 
        (a, b) => b & " "
      )
    )
  )
in
  Final

Power Query solution 4 for Add Spaces Between Types, proposed by Quadri Olayinka Atharu:

let
 Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
 NewColumn = Table.AddColumn(Source, "Answer", each let splitString = List.Accumulate(Splitter.SplitTextByCharacterTransition({"0".."9"}, (c) => not List.Contains({"0".."9"}, c))([String]), {}, (result, current) => result & Splitter.SplitTextByCharacterTransition((c) => not List.Contains({"0".."9"}, c), {"0".."9"})(current)) in Text.Combine(splitString, " "), type text)
in
 NewColumn




Funfact: I did not do any of this by tweaking any m language. This is generated by Column from example

Solving the challenge of Add Spaces Between Types with Excel

Excel solution 1 for Add Spaces Between Types, proposed by Bo Rydobon 🇹🇭:

=LET(R,LAMBDA(r,z,[y],LET(a,LEFT(z),b,MID(z,2,99),IF(AND(a=""),TRIM(y),r(r,b,y&a&REPT(" ",(a<"a")<>(b<"a")))))),R(R,A2:A8))
=LET(R,LAMBDA(r,z,[y],LET(a,LEFT(z),b,MID(z,2,99),IF(b="",y&a,r(r,b,y&a&REPT(" ",XOR(a<"a",b<"a")))))),
MAP(A2:A8,LAMBDA(a,R(R,a))))

Excel solution 2 for Add Spaces Between Types, proposed by Rick Rothstein:

=MAP(A2:A8,LAMBDA(x,LET(s,SEQUENCE(LEN(x)),p,MID(LEFT(x)&x,s,1),c,MID(x,s,1),CONCAT(IF(ISERR(-p)*ISNUMBER(-c)+ISNUMBER(-p)*ISERR(-c)," "&c,c)))))

Excel solution 3 for Add Spaces Between Types, proposed by Rick Rothstein:

=MAP(A2:A8,LAMBDA(x,LET(s,SEQUENCE(LEN(x)),p,MID(LEFT(x)&x,s,1),c,MID(x,s,1),CONCAT(IF(ISERR(-p)+ISERR(-c)=1," "&c,c)))))

Excel solution 4 for Add Spaces Between Types, proposed by محمد حلمي:

Excel solution 5 for Add Spaces Between Types, proposed by محمد حلمي:

Excel solution 6 for Add Spaces Between Types, proposed by محمد حلمي:

=LET(
a,LAMBDA(a,n,LET(

c,LEFT(n),
IF(n="","",
IF(
XOR(  IFERROR(c+1,),  IFERROR(MID(n,2,1)+1,)),
c&" ",c)  & 
a(a,MID(n,2,99))))),

MAP(A2:A8,LAMBDA(r,a(a,r))))

Excel solution 7 for Add Spaces Between Types, proposed by 🇰🇷 Taeyong Shin:

=LET(
    
     str,
     A2:A8,
    
     r,
     REDUCE(
         "",
          SEQUENCE(
              MAX(
                  LEN(
                      str
                  )
              )
          ),
          LAMBDA(
              a,
              n,
              
               LET(
                   
                    c,
                    MID(
                        str,
                         n,
                         1
                    ),
                   
                    b,
                    ISERR(
                        --c
                    ),
                   
                    a & REPT(
                        " ",
                         ISERR(
                             --RIGHT(
                                 a
                             )
                         ) <> b
                    ) & c
                    
               )
               
          )
     ),
    
     TRIM(
         r
     )
    
)
Recursive
=LET(
    
     Func,
     LAMBDA(
         ME,
         str,
         [a],
         
          LET(
              
               c,
               LEFT(
                      str
                  ),
              
               IF(
                   AND(
                       c = ""
                   ),
                    a,
                   
                    a & ME(
                        ME,
                         MID(
                             str,
                              2,
                              LEN(
                      str
                  )
                         ),
                         REPT(
                             " ",
                              ISERR(
                                  -c
                              ) <> ISERR(
                                  -RIGHT(
                                 a
                             )
                              ) 
                         ) & c
                    )
                    
               )
               
          )
          
     ),
    
     TRIM(
         Func(
             Func,
              A2:A9
         )
     )
    
)

Excel solution 8 for Add Spaces Between Types, proposed by 🇰🇷 Taeyong Shin:

=REGEXREPLACE(A2:A8,"(?<=[a-z])(?=d)|(?<=d)(?=[a-z])"," ")

Excel solution 9 for Add Spaces Between Types, proposed by 🇰🇷 Taeyong Shin:

I've always thought that character size comparison is ASCII based.

Excel solution 10 for Add Spaces Between Types, proposed by Kris Jaganah:

Excel solution 11 for Add Spaces Between Types, proposed by Julian Poeltl:

=MAP(A2:A8,LAMBDA(S,LET(SP,MID(S,SEQUENCE(LEN(S)),1),IN,ISNUMBER(--SP),CONCAT(SP&IFERROR(IF(IN<>DROP(IN,1)," ",""),"")))))

Excel solution 12 for Add Spaces Between Types, proposed by Timothée BLIOT:

=MAP(A2:A8, LAMBDA(A, CONCAT(MAP(SEQUENCE(LEN(A)), LAMBDA(x, IF(x>1,IF(ISNUMBER(VALUE(MID(A,x,1)))=ISNUMBER(VALUE(MID(A,x-1,1))),MID(A,x,1)," "&MID(A,x,1)), MID(A,x,1)) )))))

Excel solution 13 for Add Spaces Between Types, proposed by Hussein SATOUR:

=MAP(A2:A8,
 LAMBDA(x,
 TRIM(
 TAKE(
 SCAN("", MID(x, SEQUENCE(LEN(x)), 1),
 LAMBDA(x, y, IF(TYPE(--RIGHT(x)) = TYPE(--y), x & y, x & " " & y)
 )), -1))))

Excel solution 14 for Add Spaces Between Types, proposed by Sunny Baggu:

=MAP(A2:A8,LAMBDA(a,
LET(
_A,REDUCE(a,CHAR(SEQUENCE(10,,48)),LAMBDA(A,V,SUBSTITUTE(A,V," -"))),
_N,REDUCE(a,CHAR(SEQUENCE(26,,97)),LAMBDA(a,v,SUBSTITUTE(a,v," #"))),
_Asplit,TEXTSPLIT(_A," -",,FALSE),
_Nsplit,TEXTSPLIT(_N," #",,FALSE),
_comb,TRIM(CONCAT(TOCOL(VSTACK(_Asplit,_Nsplit),3,TRUE)&" ")),
_comb)))

Excel solution 15 for Add Spaces Between Types, proposed by Md. Zohurul Islam:

=LET(z,A2:A8,
a,MAP(z,LAMBDA(x,LET(
p,MID(x,SEQUENCE(LEN(x)),1),
q,ISERROR(--p),
s,IF(q,1,0),
u,VSTACK(DROP(s,1),TAKE(s,-1))=s,
v,IF(u,p,p&" "),
w,CONCAT(v),
w))),
a)

Excel solution 16 for Add Spaces Between Types, proposed by Charles Roldan:

=MAP(A2:A8, 
LAMBDA(x, TRIM(SUBSTITUTE(
REDUCE(x, SEQUENCE(10, , 0), 
LAMBDA(a, b, SUBSTITUTE(a, b, " "&b&" "))), " ", ""))))

Excel solution 17 for Add Spaces Between Types, proposed by Jaroslaw Kujawa:

=BYROW(A2:A8,
    LAMBDA(a,
    LET(
 a,
     MID(
         a,
         SEQUENCE(
             ,
             LEN(
                 a
             )
         ),
         1
     ),
    
 b,
     DROP(
         a,
         0,
         1
     ),
    
 c,
     (CODE(
                 a
             )>58)=(CODE(
                 b
             )>58),
    
 d,
     IF(
         NOT(
             ISERROR(
                 c
             )
         ),
         IF(
             c,
             a,
             a&" "
         ),
         a
     ),
    
 CONCAT(
     d
 )
)))

Excel solution 18 for Add Spaces Between Types, proposed by Guillermo Arroyo:

Excel solution 19 for Add Spaces Between Types, proposed by Guillermo Arroyo:

=MAP(A2:A8,LAMBDA(_a,LET(_f,LAMBDA(_g,_b,_c,_d,IF(_c="",_d&_b,LET(_e,LEFT(_c,1),_g(_g,_e,MID(_c,2,99),_d&_b&IF(XOR(ISNUMBER(-_e),ISNUMBER(-_b))

Excel solution 20 for Add Spaces Between Types, proposed by Stevenson Yu:

=LET(A, MID(A2, SEQUENCE(LEN(A2)),1),
B, CODE(A),
C, N(B>57),
D, RIGHT(SCAN("",C, LAMBDA(X,Y, X&Y)),2),
E, (D="10") + (D="01"),
CONCAT(IF(E=1, " "&A, A)))

Excel solution 21 for Add Spaces Between Types, proposed by Tushar Mehta:

=LAMBDA(
    _rng,
    LET(
        _doc,
        "add a space between any two characters in a string where one character is a number and the other is not",
        
         MAP(
             _rng,
             LAMBDA(
                 _text,
                 TRIM(
                     REDUCE(
                         "",
                         MID(
                             _text,
                             SEQUENCE(
                                 LEN(
                                     _text
                                 )
                             ),
                             1
                         ),
                         
                          LAMBDA(
                              _agg,
                              _str,
                              _agg&IF(
                                  ISNUMBER(
                                      --RIGHT(
                                          _agg,
                                          1
                                      )
                                  )<>ISNUMBER(
                                      --_str
                                  ),
                                  " ",
                                  ""
                              )&_str
                          )
                     )
                 )
             )
         )
    )
)(Table1[String])

Excel solution 22 for Add Spaces Between Types, proposed by Olukunle Babajide MOS, MCT:

=MAP(A2:A8,LAMBDA(c,SeparateNumChars(c)))

Solving the challenge of Add Spaces Between Types with SQL

SQL solution 1 for Add Spaces Between Types, proposed by Zoran Milokanović:

WITH /* Microsoft SQL Server 2019 */
DATA_PREP
AS
(
 SELECT
 ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS ORDERING
 ,D.STRING AS STRING
 FROM DATA D
),
CALC
AS
(
 SELECT
 DP.ORDERING
 ,DP.STRING
 ,CAST('' AS VARCHAR(MAX)) AS ANSWER
 ,CAST(DP.STRING AS VARCHAR(MAX)) AS REMAINDER
 FROM DATA_PREP DP
 UNION ALL
 SELECT
 C.ORDERING
 ,C.STRING
 ,C.ANSWER
 IIF(ASCII(SUBSTRING(REVERSE(C.ANSWER), 1, 1)) BETWEEN 48 AND 57, 'NUM', 'LET') 
 <> IIF(ASCII(SUBSTRING(C.REMAINDER, 1, 1)) BETWEEN 48 AND 57, 'NUM', 'LET') THEN ' ' ELSE '' END
 + SUBSTRING(C.REMAINDER, 1, 1) AS ANSWER
 ,SUBSTRING(C.REMAINDER, 2, LEN(C.REMAINDER) - 1) AS REMAINDER
 FROM CALC C
 WHERE
 C.REMAINDER <> ''
)
SELECT
 C.STRING
,C.ANSWER
FROM CALC C
WHERE
 C.REMAINDER = ''
ORDER BY
 C.ORDERING
;

Solving the challenge of Add Spaces Between Types with Power Query

Solving the challenge of Add Spaces Between Types with Excel

Solving the challenge of Add Spaces Between Types with SQL

Leave a Reply Cancel reply