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

--
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"AA2e72E" wrote in message ...
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.

"Ange" wrote:

Hello.

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

V5R5H6 to V5R 5H6 I need a formula.

Thank you !