Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA inserting a space in a text
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA inserting a space in a text
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. -- Gary's Student "Mouimet" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA inserting a space in a text
On Thu, 9 Nov 2006 08:39:02 -0800, Mouimet
wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
VBA inserting a space in a text
Thank you very much,
This is exacly what I need. Have a nice day "Gary''s Student" wrote: 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. -- Gary's Student "Mouimet" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I remove a space infront of text in a cell? | Excel Discussion (Misc queries) | |||
removing a space from starting of the text | Excel Worksheet Functions | |||
Inserting a Space in The Same Place Formula | Excel Worksheet Functions | |||
space between text strings with concatenate | Excel Discussion (Misc queries) | |||
Read Text File into Excel Using VBA | Excel Discussion (Misc queries) |