VBA inserting a space in a text

[Guest]

Hi,
I need to create a macro to convert a canadian postal code.
The user received different files with the postal code in a wrong format.
The data are not always in the same column.
I just want the user to select the column or the range then click the macro
to convert it in the right format.
Format they received: h2r4d5
After they click the macro I need to see H2R 4D5 (space after the third
digit)
Normaly I do this using another column with the formula =left(a1,3)&"
"&right(a1,3)
Then copie the value to replace the data.
It will be faster if we can do this in VBA and the userid doesn't need to
know how to type the formula.
Thank You

 

[Guest]

This will look familiar:


Sub go_postal()
Dim r As Range, v As String
For Each r In Selection
v = r.Value
r.Value = Left(v, 3) & " " & Right(v, 3)
Next
End Sub

Just select the cells (or columns) and run the macro. VBA has the advantage
that you do not need a helper column.

 

[Ron Rosenfeld]

Hi,
I need to create a macro to convert a canadian postal code.
The user received different files with the postal code in a wrong format.
The data are not always in the same column.
I just want the user to select the column or the range then click the macro
to convert it in the right format.
Format they received: h2r4d5
After they click the macro I need to see H2R 4D5 (space after the third
digit)
Normaly I do this using another column with the formula =left(a1,3)&"
"&right(a1,3)
Then copie the value to replace the data.
It will be faster if we can do this in VBA and the userid doesn't need to
know how to type the formula.
Thank You

Perhaps something like:

==================================
Option Explicit
Sub FormatPostCode()
Dim c As Range
Dim i As Long
Dim sCode As String

For Each c In Selection
sCode = Replace(c.Text, " ", "")
sCode = UCase(sCode)

'check for valid post code
If Len(sCode) <> 6 Then Exit Sub
'or display some error message

sCode = Left(sCode, 3) & " " & Right(sCode, 3)
c.Value = sCode
Next c
End Sub
==========================
--ron

 

[Guest]

Thank you very much,
This is exacly what I need.
Have a nice day