Delete (.) Then Combine 3 Last Number With Mark (-)

[muhammad susanto]

hi all...

how to modify data like this

<style type="text/css">
table.tableizer-table {
font-size: 12px;
border: 1px solid #CCC ;
font-family: Arial, Helvetica, sans-serif;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #CCC ;
}
.tableizer-table th {
background-color: #104E8B ;
color: #FFF ;
font-weight: bold;
}
</style>

data	expected result
4.01.01.01.001.010	4010101001-010
4.01.01.05.001.007	4010105001-007
4.01.01.05.001.342	4010105001-342

<tbody>
</tbody>

any help, thanks advance

.sst

 

[lrobbo314]

A formula.

=SUBSTITUTE(LEFT(A2,LEN(A2)-4),".","")&"-"&RIGHT(A2,3)

Or PowerQuery...

let
    Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
    Split = Table.SplitColumn(Source, "Data", Splitter.SplitTextByEachDelimiter({"."}, QuoteStyle.Csv, true), {"Data.1", "Data.2"}),
    Replace = Table.ReplaceValue(Split,".","",Replacer.ReplaceText,{"Data.1"}),
    Merge = Table.AddColumn(Replace, "Merged", each Text.Combine({[Data.1], Text.From([Data.2], "en-US")}, "-"), type text),
    Remove = Table.RemoveColumns(Merge,{"Data.1", "Data.2"})
in
    Remove

 

[lrobbo314]

This formula too.

=SUBSTITUTE(REPLACE(A2,LEN(A2)-3,1,"-"),".","")

 

[muhammad susanto]

hi..
thanks but not fully work..i mean the mark (.) still show..

 

[lrobbo314]

I just saw that. I updated the formulas in the previous posts. They should be correct now.

 

[muhammad susanto]

hi Irobbo314.....

yes..it's right...Thanks a lot..

 

[Rick Rothstein]

If your values always have exactly 5 dots in them...

=SUBSTITUTE(SUBSTITUTE(A1,".","-",5),".","")