Insert marker in text

[vhobbs1]

I need to insert a marker (like "/") between a zip code and an occupation in a text cell in order to separate the text into two cells. How can I devise a formula that will "find" the 5-digit zip code, then insert a marker after the zip? Help!

Before:
221 South Union # 1 Jackson MO 63755 Lutheran Pastor

After:
221 South Union # 1 Jackson MO 63755 / Lutheran Pastor

 

[hiker95]

vhobbs1,

Sample data before the macro:

Excel Workbook
	A
1	221 South Union # 1 Jackson MO 63755 Lutheran Pastor
2	221 South Union # 1 Jackson MO 01010 Lutheran Pastor
3	221 South Union # 1 Jackson City MO 12345 Lutheran Pastor
4
Sheet1

After the macro:

Excel Workbook
	A
1	221 South Union # 1 Jackson MO 63755 / Lutheran Pastor
2	221 South Union # 1 Jackson MO 01010 / Lutheran Pastor
3	221 South Union # 1 Jackson City MO 12345 / Lutheran Pastor
4
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Adding the Macro
1. Copy the below macro, by highlighting the macro code and pressing the keys CTRL + C
2. Open your workbook
3. Press the keys ALT+F11 to open the Visual Basic Editor
4. Press the keys ALT+I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL + V
7. Press the keys ALT+Q to exit the Editor, and return to Excel

Option Explicit
Sub InsertMarker()
Dim c As Range, Sw As Long, a As Long
Application.ScreenUpdating = False
For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
  Sw = 0
  For a = 1 To Len(c) Step 1
    Select Case Mid(c, a, 1)
      Case "0" To "9"
        Sw = Sw + 1
      Case " "
        If Sw = 5 Then
          c = Left(c, a) & "/ " & Right(c, (Len(c) - a))
          Exit For
        Else
          Sw = 0
        End If
    End Select
  Next a
Next c
Application.ScreenUpdating = False
End Sub

Then run the "InsertMarker" macro.

 

[hiker95]

vhobbs1,

And, welcome to the MrExcel board.

 

[bosco_yip]

Or try……

=REPLACE(A1,LOOKUP(2,-MID(A1,ROW($1:$99),1),ROW($1:$99))+1,1," / ")

Regards
Bosco

 

[hiker95]

bosco_yip,

Very nice.

I was trying to find some information on patterns to solve the OP's request.


Can you explain how the formula works?

 

[Norie]

Was looking into regular expressions and cam up with these UDFs.

Function GetZip(rng As Range)
Dim RegEx As Object
Dim RegMatchCollection As Object
    Set RegEx = CreateObject("vbscript.regexp")
    With RegEx
        .Global = True
        .Pattern = "([0-9][0-9][0-9][0-9][0-9])"
    End With
    Set RegMatchCollection = RegEx.Execute(rng.Value)
    
    GetZip = RegMatchCollection(0).firstindex
    
End Function
 
Function InsertMarker(rng As Range)
    InsertMarker = Left(rng, GetZip(rng) + 6) & "/" & Mid(rng, GetZip(rng) + 6)
    
End Function

 

[wsjackman]

two questions

do you ever have numbers in the entry after the zip?

more important

do you have any of the 9 digit zips?

 

[hiker95]

Norie,

Thank you very much.

 

[wsjackman]

if the title never has more than two words the following formula will work

if the title has more than 2 words the formula could be adjusted but the macros look better and better.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K
25	221 South Union # 1 Jackson MO 63755 Lutheran Pastor						221 South Union # 1 Jackson MO 63755 / Lutheran Pastor
26	222 South Union # 1 Jackson MO 63755 Lutheran						222 South Union # 1 Jackson MO 63755 / Lutheran
27	Jackson MO 63755 Lutheran Pastor						Jackson MO 63755 / Lutheran Pastor
28	223 South Union # 1 Jackson MO 99357 Lutheran						223 South Union # 1 Jackson MO 99357 / Lutheran
29	223 South Union # 1 Jackson MO 63755 Lutheran Pastor						223 South Union # 1 Jackson MO 63755 / Lutheran Pastor
30	224 South Union # 1 Jackson MO 63755 Lutheran						224 South Union # 1 Jackson MO 63755 / Lutheran
Sheet2

 

[Norie]

hiker95

No problem.

Mind you I think I just lucked out on this one - I've been trying to fully understand regular expressions for ages.

Only ever use/try to use them every now and again - normally when something comes up here.

But I've never been able to find a comprehensive guide to them.

So I don't know if what I've posted is fully reliable or robust, but it seems to work for the posted data.