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

 

[Domski]

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.

Hi Norie,

I've seen them used here but always been a bit of a mystery to me. Came across this when I tried (and failed) to answer a post using them not long ago:

http://www.regular-expressions.info/index.html

I'm sure they make sense once you've got your head round the syntax but it's all Dutch to me at the moment.

Dom

 

[Norie]

Dom

That's actually the site I was looking at earlier.

Seems better than some of the others I tried - even looked at Wikipedia.

But like you say it's all a bit Dutch, and I've also got a feeling that regular expessions aren't that 'regular' depending on what app/language you are using.

 

[Norie]

Just tried another UDF.

Option Explicit
Function GetZip(rng As Range)
Dim RegEx As Object
Dim RegMatchCollection As Object
Dim n
    Set RegEx = CreateObject("vbscript.regexp")
    With RegEx
        .Global = True
        .Pattern = "([0-9]{5})"
    End With
    Set RegMatchCollection = RegEx.Execute(rng.Value)
    
    GetZip = RegMatchCollection(0)
    
End Function

I like it better because it appears to extract the zip code, assuming it's 5 numbers.

Oops, just did a little bit of a test - seems to pick up anything that starts with 5 numbers.

 

[hiker95]

Norie, Domski,

Two more sites that may also be helpful (supplied by Leith Ross, Forum Moderator at ExcelForum.com):

Microsoft Beefs Up VBScript with Regular Expressions
http://msdn.microsoft.com/en-us/library/ms974570.aspx

VBScript User's Guide
http://msdn.microsoft.com/en-us/library/sx7b3k7y(VS.85).aspx

 

[Norie]

Hiker95

I think I've looked at both those sites, but I've still not found anything comprehensive.

I'm probably missing something, or just can't be bothered to look through the links/sites properly.

Anyways, I'll be off to bed as soon as I remember the name of that guy who was in a late 70's film with cars and stuff.

Barry something...

Anyways, he seems to have popped up in Quincy ME as a dodgy doctor.