Add Leading Zeroes with an Excel Formula - Two Examples

In this video, we add leading zeroes to values using an Excel formula.

Excel can remove the leading zeroes when entered into a cell or imported from external sources. This can cause problems with your Excel formulas.

XLOOKUP is a function that cannot match values of different formats. In this example, one is text and the others are numbers.

There are many techniques to add leading zeroes in Excel. This video shows two formula examples.

The video timings.
00:00 - Numbers missing leading zeroes
00:24 - COUNTIFS recognising values
01:24 - XLOOKUP cannot match values
02:07 - Add zeroes with custom number formatting
03:07 - TEXT function to add leading zeroes for XLOOKUP
04:39 - REPT to pad a value with leading zeroes

Find more great free tutorials at;

*** Online Excel Courses ***

Connect with us!

[m.raedallulu]

Beside you present a brilliant useful techniques, I love the way you speak, Alan!

[wayneedmondson]

Nice one Alan! Here's an alternate approach.. inside XLOOKUP, convert the lookup_array to values using a double-unary and parenthesis, as in: =XLOOKUP(E2, --($A$2:$A$13), $B$2:$B$13, ""). Just another way to get there. Thanks for your tips! Thumbs up!!

[IvanCortinas_ES]

Thank you for this video. Very useful, Alan!!!

[JoseAntonioMorato]

Dear Alan,
A third way of solving the problem:
=XLOOKUP(E3, VALUE(A2:A13), B2:B13, "")
or
=XLOOKUP(E3:E6, VALUE(A2:A13), B2:B13, "") - spilled🤗

[InsideMyWall]

I have written L=100+200 in A1. I want the sum of 100+200=300 in A2. How can I do this ??