Place space in postal code

[Ange]

Hello.

All I need to do is create a space in a postal code.

V5R5H6 to V5R 5H6 I need a formula.

Thank you !

 

[Tom Ogilvy]

if it is always the 4th position

dim cell as Range, sStr as String
for each cell in selection
sStr = trim(cell.value)
if len(sStr) > 4 then
cell.value = left(sStr,3) & " " & Mid(sStr,4)
end if
next

 

[Guest]

Try:

Format("V5R5H6","&&& &&&")

& is the placeholder for characters in the source string, any other
character (space in this case) is always inserted at the position it occurs
in the format string.

 

[Jamie Collins]

All I need to do is create a space in a postal code.

if it is always the 4th position

It's not <g>. OP should see:

http://www.evoxfacilities.co.uk/evoxps.htm

Jamie.

--

 

[Tom Ogilvy]

Here is the revision

dim cell as Range, sStr as String
for each cell in selection
sStr = trim(cell.value)
if len(sStr) >= 5 and <= 7 then
cell.value = left(sStr,len(sStr)-3) & " " & right(sStr,3)
end if
next

 

[David McRitchie]

Format("V5R5H6","&&& &&&")

Interesting, learn something new every day, I thought the VBA
Format function was same as used in Excel, Format, cells.

But the formatting
is done as if left justified and will convert "abcd" to "a bcd"
so would need the the check for a length of 6.

Since so much time is being spent on this, here is a solution
that makes sure you have letter-digit-letter-digit-letter-digit
to begin with before converting to letter-digit-letter-space-digit-letter-digit
used in Canadian zip codes. Those letters are all caps.

Sub FixCANADAzips()
'David McRitchie 2004-11-02 notposted, updated
'http://www.mvps.org/dmcritchie/excel/join.htm#fixuszips
'you might want to run TRIMALL macro before this macro
Application.ScreenUpdating = False
Application.Calculation = xlManual
Dim cell As Range, str As String
Selection.Replace What:=Chr(160), replacement:=Chr(32), _
lookat:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
'Trim in Excel removes extra internal spaces, VBA does not
On Error Resume Next
For Each cell In Selection.SpecialCells(xlConstants, 2)
'you might want to run TRIMALL macro before this macro
If cell Like "[A-Z]#[A-Z]#[A-Z]#" Then
cell.Value = Left(cell.Value, 3) & " " & Mid(cell.Value, 4)
End If
Next cell
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
End Sub

--

 

[David McRitchie]

meant as if *right* justified and will convert
"abcd" to "a bcd"

 

[Tom Ogilvy]

Letter digit letter digit letter digit
is not the format of the postal codes. See Jamie Collins' link.

 

[David McRitchie]

It is for Canadian zip codes see Angie's post.
Of course it needs the space that was asked for.
Letter digit Letter space digit Letter digit

 

[Jamie Collins]

It is for Canadian zip codes see Angie's post.

Oops! Apologies for the red herring.

Jamie.

--