CODEWITHSUNDEEP
excelvlookupleading-zero
Muhammad Waseem
Muhammad Waseem

Reputation: 21

Adding Leading Zeros to Multiple Cells in Excel at Once

I have been working on a database which needs VLOOKUP to another database. The lookup table displays values without leading zeros as:

5625698
2586585
2479653
9985632
1125478

To be specific, I want to convert these numbers to display preceding zeroes. Instead of editing each cell separately and converting it to Text Format, I want to know whether there is a shortcut to add preceding zeroes to multiple cells at once.

Regards

Upvotes: 0

Views: 1309

Answers (3)

Mubashar
Mubashar

Reputation: 12658

There is an easy way to do it. Ok I think what you are doing here is that trying to reformat phone numbers which excel being over-smart broke in first place. Anyway you can use following steps

  1. Select cells, Right Click, Click Format Cell
    enter image description here
  2. Format Cell dialogue will appear, choose custom from left list and type zeroes upto max length e.g. for mobile phone number its ten digits in Australia including leading zero.
    enter image description here
  3. press ok. And voila!!
    enter image description here
  4. Bonus: if you like to format you phone numbers with parenthesis () and/or with spaces you can do the same way by adding them in format string

enter image description here

Upvotes: 0

Muhammad Waseem
Muhammad Waseem

Reputation: 21

I found the answer at www.extendoffice.com

The solution is to either use the CONCENTRATE function in a different column, which is similar to the suggestion of EEM (but this is not what I was looking for). Or use VBA Module with the following code:

Sub AddTextOnLeft()
'Updateby20161004
Dim Rng As Range
Dim WorkRng As Range
Dim addStr As String
On Error Resume Next
xTitleId = "Add Text"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
addStr = Application.InputBox("Add text", xTitleId, "", Type:=2)
For Each Rng In WorkRng
    Rng.Value = addStr & Rng.Value
Next
End Sub

Upvotes: 0

EEM
EEM

Reputation: 6659

Use this formula to add leading zeros and convert the number to text

=TEXT(XXX,"00000000")

Upvotes: 0

Related Questions