Hello.

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

V5R5H6 to V5R 5H6 I need a formula.

Thank you !

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

--
Regards,
Tom Ogilvy

"Ange" wrote in message
om...
Hello.

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

V5R5H6 to V5R 5H6 I need a formula.

Thank you !

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 !

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.

--

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

--
Regards,
Tom Ogilvy


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

--

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 !

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

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

--
Regards,
Tom Ogilvy

"David McRitchie" wrote in message
...
meant as if *right* justified and will convert
"abcd" to "a bcd"

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


"Tom Ogilvy" wrote in message ...
Letter digit letter digit letter digit
is not the format of the postal codes. See Jamie Collins' link.

"David McRitchie" wrote ...

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

Oops! Apologies for the red herring.

Jamie.

--