Text.Insert Function

[jilbobagins]

Hi all,

Looking at adding characters into a part number at given lengths. ie


2587449861234

2587-44-986-1234


So I've tried using Text.Insert([Part Number], 4,"-") and that gives me 2587-449861234

How do I neatly add the other positions to add the "-" (4,6,9)? Can Splitter.SplitTextByPositions be used?

Thanks in advance

 

[horseyride]

I would go with some combination of Text.Middle, Text.Start and Text.End
You can mix and match

= Table.AddColumn(#"Changed Type", "Custom", each Text.Middle([a],0,4)&"-"&Text.Middle([a],4,2)&"-"&Text.Middle([a],6,3)&"-"&"-"&Text.Middle([a],9,4))

= Table.AddColumn(#"Changed Type", "Custom", each Text.Start([a],4)&"-"&Text.Middle([a],4,2)&"-"&Text.Middle([a],6,3)&"-"&"-"&Text.End([a],4))


DAX would use something along the lines of FORMAT(TABLE[PHONENO],"####-##-###-####") but that doesnt help here

 

[jilbobagins]

Once again you've sorted it, I need to buy you lots and lots of Pints!!!

 

[Ron Coderre]

A little late...(Saw this this morning, but had to commute in to work)

You could add a column of text formatted the way you want:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    SetDataTypes = Table.TransformColumnTypes(Source,{{"MyNumber", Int64.Type}}),
    [COLOR=#0000ff]AddColOfFormarttedText = Table.AddColumn(SetDataTypes, "Custom", each Number.ToText([MyNumber],"0000-000-00-0000"), type text)[/COLOR]
in
    AddColOfFormarttedText

Or you could transform the number into formatted text:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    SetDataTypes = Table.TransformColumnTypes(Source,{{"MyNumber", Int64.Type}}),
     [COLOR=#0000ff]TransformToFormattedText = Table.TransformColumns(SetDataTypes,  {{"MyNumber", each Number.ToText(_,"0000-000-00-0000"), type text}})[/COLOR]
in
    TransformToFormattedText

These are the results for the second method:

MyNumber
2587-449-86-1234
4444-333-22-1234
1111-222-33-4321
0011-115-59-8876

Hope that helps

 

[jilbobagins]

Thank You Ron as well, I'm going to give this a go as well!....I can also use this on something else I'm working on.