Place space in postal code
 

Hello.

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

V5R5H6 to V5R 5H6 I need a formula.

Thank you !

Place space in postal code
 

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 !

Place space in postal code
 

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 !

Place space in postal code
 

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.

--

Place space in postal code
 

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.

--

Place space in postal code
 

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 !

Place space in postal code
 

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

Place space in postal code
 

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"

Place space in postal code
 

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.

Place space in postal code
 

"David McRitchie" wrote ...

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

Oops! Apologies for the red herring.

Jamie.

--