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?
 

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
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
2,377
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
54,380
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,497

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,497

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,497
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,497
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:
Master Excel Bundle

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.

Forum statistics

Threads
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.
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