Trim Zipcode

[Muthukrishnan V]

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.

 

[alansidman]

Here is a VBA solution for you

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

 

[jtakw]

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.)

 

[Peter_SSs]

Another vba option that does them all at once.

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?