# Add Zeros to the middle of a cell

#### Jonezee1

##### New Member
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?

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

#### mth0012

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

#### kweaver

##### Well-known Member
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)

Last edited:

#### Peter_SSs

##### MrExcel MVP, Moderator
Welcome to the MrExcel board!

For any length text you could try this

20 07 24.xlsm
AB
1Gi1/0/1Gi1/0/01
2GI1/0/11GI1/0/11
3abcdef5/8/5abcdef5/8/05
4abcdef5/8/56abcdef5/8/56
Cell Formulas
RangeFormula
B1:B4B1=REPLACE(A1,LEN(A1),0,IF(ISNUMBER(RIGHT(A1,2)+0),"",0))

#### sandy666

##### Banned - Rules violations

just for fun with Power Query
Rich (BB code):
``````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

#### sandy666

##### Banned - Rules violations

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

Last edited:

#### sandy666

##### Banned - Rules violations
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

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

Rich (BB code):
``````let Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content]

Last edited:

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,164,199
Messages
5,835,936
Members
430,396
Latest member
dzifna

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back