Trim Zipcode

Muthukrishnan V

Active Member
Joined
May 29, 2008
Messages
294
Office Version
  1. 365
Platform
  1. Windows
MS Excel 2007. I have an address data base in which Column J contains Zipcode in 6 digits. I have this Column J formatted under category "Text". For convenience I have entered only last 3 digits, for example 096 in J2.

My requirement: A formula for Column J. J2 displays 096. I want this to be prefixed with
600. The resultant output in J2 should be 600096.
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Here is a VBA solution for you

Code:
Option Explicit


Sub AddZip()
Dim i As Long, lr As Long
lr = Range("J" & Rows.Count).End(xlUp).Row
For i = 2 To lr
Range("J" & i) = 600 & Range("J" & i)
Next i
End Sub
 
Upvote 0
Hi,

You can easily do this with Custom Formatting.
Select the cells you want formatted in Column J
Right click, Format Cells
Choose Custom, enter 600000

Whenever you enter your 3 digit number, it'll automatically show as 6 digits ( 092 becomes 600092, 123 becomes 600123, etc.)
 
Upvote 0
Another vba option that does them all at once.
Code:
Sub CompleteZip()
  With Range("J1", Range("J" & Rows.Count).End(xlUp))
    .Value = Evaluate(Replace("if(#="""","""",if(len(#)=3,600&#,#))", "#", .Address))
  End With
End Sub

Your thread title mentions "Trim" - was there supposed to be anything about that in your question?
 
Upvote 0

Forum statistics

Threads
1,223,897
Messages
6,175,271
Members
452,628
Latest member
dd2

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
Back
Top