Add Zeros to the middle of a cell

Jonezee1

New Member
Joined
Jul 23, 2020
Messages
1
Office Version
  1. 2016
Platform
  1. Windows
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?
 

Some videos you may like

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

mth0012

New Member
Joined
Jul 22, 2020
Messages
8
Office Version
  1. 2016
Platform
  1. Windows
try =IF(LEN(CELL)=8,A1,LEFT(CELL,6)&"0"&RIGHT(CELL,1))
where CELL is your cell (A1 etc.)
 

kweaver

Well-known Member
Joined
May 8, 2018
Messages
1,420
Office Version
  1. 365
  2. 2010
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
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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
Add Zero
Cell Formulas
RangeFormula
B1:B4B1=REPLACE(A1,LEN(A1),0,IF(ISNUMBER(RIGHT(A1,2)+0),"",0))
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499

ADVERTISEMENT

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
Column1Insert
Gi1/0/1Gi1/0/01
GI1/0/11GI1/0/011
abcdef5/8/5abcdef5/8/05
abcdef5/8/56abcdef5/8/056
ZXCV/bb/rtu/999/75ZXCV/bb/rtu/999/075
abc/879/134/444444abc/879/134/0444444
 

sandy666

Banned - Rules violations
Joined
Oct 24, 2015
Messages
7,499

ADVERTISEMENT

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
Joined
Oct 24, 2015
Messages
7,499
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
Joined
Oct 24, 2015
Messages
7,499
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]
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"})
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,328
Members
414,054
Latest member
Sameer50

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top