20 07 24.xlsm | ||||
---|---|---|---|---|
A | B | |||
1 | Gi1/0/1 | Gi1/0/01 | ||
2 | GI1/0/11 | GI1/0/11 | ||
3 | abcdef5/8/5 | abcdef5/8/05 | ||
4 | abcdef5/8/56 | abcdef5/8/56 | ||
Add Zero |
Cell Formulas | ||
---|---|---|
Range | Formula | |
B1:B4 | B1 | =REPLACE(A1,LEN(A1),0,IF(ISNUMBER(RIGHT(A1,2)+0),"",0)) |
ADVERTISEMENT
let
Source = Excel.CurrentWorkbook(){[Name="Table3"]}[Content],
Insert = Table.SelectColumns(Table.AddColumn(Table.AddColumn(Source, "Pos", each Text.PositionOf([Column1], "/", Occurrence.Last)+1), "Insert", each Text.Insert([Column1], [Pos], "0")),{"Insert"})
in
Insert
Column1 | Insert | |
Gi1/0/1 | Gi1/0/01 | |
GI1/0/11 | GI1/0/011 | |
abcdef5/8/5 | abcdef5/8/05 | |
abcdef5/8/56 | abcdef5/8/056 | |
ZXCV/bb/rtu/999/75 | ZXCV/bb/rtu/999/075 | |
abc/879/134/444444 | abc/879/134/0444444 | |
That isn't the outcome requested.just for fun with Power Query
GI1/0/11 should be left alone.
ADVERTISEMENT
thanks PeterThat isn't the outcome requested.
let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content]
in Table.SelectColumns(Table.AddColumn(Source, "Result", each Text.Format("#{0}/#{1}",{Text.BeforeDelimiter([Column1], "/", {0, RelativePosition.FromEnd}),Text.PadStart(Text.AfterDelimiter([Column1], "/", {0, RelativePosition.FromEnd}),2,"0")})),{"Result"})