Search code examples
excelvlookupleading-zero

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

Solution

  • 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