Add Zeros to the middle of a cell

[Jonezee1]

Hello,
I'm trying to add a zero to the middle of a cell. i.e. GI1/0/1 should be Gi1/0/01, GI1/0/11 should be left alone. Can anyone help please?

 

[mth0012]

try =IF(LEN(CELL)=8,A1,LEFT(CELL,6)&"0"&RIGHT(CELL,1))
where CELL is your cell (A1 etc.)

 

[kweaver]

Is the length of each cell always 7 or 8? Is the end of the string in a cell always 2-digits or 1?

Try: =IF("/"=LEFT(RIGHT(A1,2),1),LEFT(A1,LEN(A1)-1)&"0"&RIGHT(A1,1),A1)

 

[Peter_SSs]

Welcome to the MrExcel board!

For any length text you could try this

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))

 

[sandy666]

just for fun with Power Query

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

 

[Peter_SSs]

just for fun with Power Query

That isn't the outcome requested.

GI1/0/11 should be left alone.

 

[sandy666]

That isn't the outcome requested.

thanks Peter ?
but I don't know it is a single case or for each double number /11, /55, /99
request isn't clear and very small example

 

[sandy666]

so maybe

Result
Gi1/0/01
GI1/0/11
abcdef5/8/05
abcdef5/8/56
ZXCV/bb/rtu/999/75
abc/879/134/444444

and I will wait for better description and bigger example with the proper result

 

[sandy666]

I think it will be a long wait for the description and example, so I will write M without wasting time

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"})