How to add ":" after 2 character

[Mohammad Younous]

2b95ty6jf98w
How to add ":" after 2 character? Like 2b:95:ty:6j:f9:8w.

 

[bosco_yip]

Try..........

In B1, copied down :

=TEXTJOIN(":",,MID(A1,MMULT(ROW(INDIRECT("1:"&ROUNDUP(LEN(A1)/2,0)))*2-1,1),2))

 

[Peter Bartholomew]

A basic solution might be like that from @bosco_yip
= TEXTJOIN(":", ,
MID(@string&" ", 2*ROW(INDIRECT("1:"&QUOTIENT(1+LEN(@string),2)))-1, 2 ))
With MS365 beta that could be
= LET(
n, LEN(string),
k, SEQUENCE(QUOTIENT(1+n, 2), 1, 0),
pairs, MID(string, 2*k+1, 2 ),
TEXTJOIN(":", , pairs) )
or using Regular Expressions within Charles Williams's rgx.MID function
= LET(
pairs, Rgx.MID(string,"\w{2}|\w$",0),
TEXTJOIN(":",,pairs) )
where "\w" returns characters and {2} requires a pair of preceding token.

 

[AlanSidman]

Also can be accomplished using Power Query/Get and Transform. Two Steps. Split Column by 2 digits. Merge all columns with colon as a separator.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Position" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByRepeatedLengths(2), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6"}),
    #"Merged Columns" = Table.CombineColumns(Table.TransformColumnTypes(#"Split Column by Position", {{"Column1.2", type text}}, "en-US"),{"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged")
in
    #"Merged Columns"

Data Range

	A​
1​	Merged​
2​	2b:95:ty:6j:f9:8w​
3​	2b:95:ty:6j:f9:8w​
4​	2b:95:ty:6j:f9:8w​
5​	2b:95:ty:6j:f9:8w​
6​	2b:95:ty:6j:f9:8w​
7​	2b:95:ty:6j:f9:8w​
8​	2b:95:ty:6j:f9:8w​
9​	2b:95:ty:6j:f9:8w​
10​	2b:95:ty:6j:f9:8w​
11​	2b:95:ty:6j:f9:8w​

 

[Peter Bartholomew]

@AlanSidman
I worked well on a static dataset. How did you / would you address data refresh involving longer data strings?

 

[AlanSidman]

Peter, An excellent question. I did a bit of research on this and here is the Mcode and a sample file.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Position" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByRepeatedLengths(2), {"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6", "Column1.7", "Column1.8"}),
    #"Merged Columns" = Table.CombineColumns(#"Split Column by Position",{"Column1.1", "Column1.2", "Column1.3", "Column1.4", "Column1.5", "Column1.6", "Column1.7", "Column1.8"},Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged"),
    #"Trimmed Text" = Table.TransformColumns(#"Merged Columns",{{"Merged", each Text.TrimEnd(_,":"), type text}})
in
    #"Trimmed Text"

 

[Chihiro]

To make it truly dynamic you'll need to generate column names via function/logic.

Otherwise, string with length greater than defined number of columns will be truncated.

One such method:
1. Add custom column.
=Text.Length([Column1]

2. Add vColCount in Advanced Query editor.
= Number.RoundUp(List.Max(#"Added Custom"[Custom])/2)

3. Add vColList in Advanced Query editor.
= List.Generate(()=> [x = 1, y = "Col1"], each [x] <= vColCount, each [x = [x]+1, y = "Col" & Text.From(x)], each [y])

Use vColList to supply list of column names to Split column & Merged columns.

Complete M:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Text.Length([Column1])),
    vColCount = Number.RoundUp(List.Max(#"Added Custom"[Custom])/2),
    vColList = List.Generate(()=> [x = 1, y = "Col1"], each [x] <= vColCount, each [x = [x]+1, y = "Col" & Text.From(x)], each [y]),
    #"Split Column by Position" = Table.SplitColumn(Source, "Column1", Splitter.SplitTextByRepeatedLengths(2), vColList),
    #"Merged Columns" = Table.CombineColumns(#"Split Column by Position",vColList,Combiner.CombineTextByDelimiter(":", QuoteStyle.None),"Merged"),
    #"Trimmed Text" = Table.TransformColumns(#"Merged Columns",{{"Merged", each Text.TrimEnd(_,":"), type text}})
in
    #"Trimmed Text"

Note: I cheated here, and changed reference for #"Split Column by Position" to Source. So that remove column step is unnecessary for the added custom column.