Explanation
This formula relies on a table with columns for both the full state name and the 2-letter abbreviation. Because we are using VLOOKUP, the full name must be in the first column. For simplicity, the table has been named “states”.
VLOOKUP is configured to get the lookup value from column C. The table array is the named range “states”, the column index is 2, to retrieve the abbreviation from the second column). The final argument, range_lookup, has been set to zero (FALSE) to force an exact match.
=VLOOKUP(C5,states,2,0)
VLOOKUP locates the matching entry in the “states” table, and returns the corresponding 2-letter abbreviation.
Generic mapping
This is a good example of how VLOOKUP can be used to convert values using a lookup table. This same approach can be used to lookup and convert many other types of values. For example, you could use VLOOKUP to map numeric error codes to human readable names.
Reverse lookup
What if you have a state abbreviation, and want to lookup the full state name using the lookup table in the example? In that case, you’ll need to switch to INDEX and MATCH. With a lookup value in A1, this formula will return a full state name with the lookup table as shown:
=INDEX(G5:G55,MATCH(A1,H5:H55,0))
If you want to use the same named range “states” you can use this version to convert a 2-letter abbreviation to a full state name.
=INDEX(INDEX(states,0,1),MATCH(A1,INDEX(states,0,2),0))
Here, we use INDEX to return whole columns by supplying a row number of zero. This is a cool and useful feature of the INDEX function : if you supply zero for row, you get whole column(s) if you supply zero for column, you get whole row(s).
Explanation
In this example, the goal is to add leading zeros to a given number so that the total number of characters displayed is 5. Sometimes this is referred to as “padding” a number with zeros, because the number of zeros needed is variable. If the original number contains 2 digits, 3 zeros are added. If the original number contains 3 digits, 2 zeros are added, and so on.
There are two basic ways to solve this problem: (1) convert the number to text with leading zeros (2) apply a custom number format to display the number with leading zeros. The best approach depends on your needs. If numbers really should be text values, use Option 1. If numbers need to remain numeric values, use Option 2. Both options are easy to implement, but Option 1 is more flexible.
Option 1 - convert to text
A simple way to add leading zeros to a number is to use the TEXT function . The TEXT function returns a number formatted as text, using the number format provided. In this case, we want the final result to have five characters total, so the number format includes five zeros: “00000”. This number format is used directly in the TEXT function as the format_text argument:
=TEXT(B5,"00000") // returns "00127"
The result is the text string “00127”. Note that Excel will automatically align text values to the left, but you can manually set alignment as desired. To replace the original numbers with the converted text values: first copy the converted values, then select the original numbers and use Paste Special > Values to overwrite the original numbers.
Related video: How to do in place changes with Paste Special
Option 2 - apply number format
Another way to add leading zeros to a number is to apply a custom number format to display the numbers with leading zeros. The key thing to understand with this option is that the numeric values underneath are not affected . Applying a number format only changes the way the numbers are displayed in Excel .
To apply a number format to display leading zeros, select the values in F5:F16 and use the shortcut Control + 1 to display the Format Cells window:
Then navigate to the Number tab, select “Custom” and enter 00000 in the “Type” input area:
Note the Sample area will show an example of the format applied to the first cell in the selection. When you click “OK” the number format is applied and numbers are displayed with leading zeros. Note the formula bar will continue to show the numeric value in its original form:
Summary
Both options above work well for adding leading zeros to a number. The key difference is that Option 1 results in text , while Option 2 preserves the number . Be aware that you may need to cater to this difference in other formulas. For example, if you are performing a lookup operation with VLOOKUP or INDEX and MATCH , you must match the lookup value to the data. The formulas below show how this difference affects a VLOOKUP formula:
=VLOOKUP("00127",range,column,FALSE) // look up text
=VLOOKUP(127,range,column,FALSE) // look up number
More on VLOOKUP here .
