Split before Zip Code

**nordicdust** · 07-19-2020, 02:55 PM

Hi guys

I have a application where I extract a address from folder names.
The address is not always how it should be acording to our rules for creating case folders.

So I am trying to split it and delete the parts that should not be there.

This is the folderPath "X:\IN4MO\TILBUDSSAGER FORSIKRINGSAFDELINGEN\Frederik d. II's Allé 335, 8660 Skanderborg"
Then I extract The address "Frederik d. II's Allé 335, 8660 Skanderborg"
Then I Replace the comma with Space "Frederik d. II's Allé 335 8660 Skanderborg"
Then I Replace double space with single space "Frederik d. II's Allé 335 8660 Skanderborg"

Here is where I am stuck.
I want to split the string Before the Zip code that is always 4 digit.

I have made a sample file with the code working up to where to split at Zip code, I have no idear how to do that.

I hope some one can guide me to a solution.

**TMS** · 07-19-2020, 04:54 PM

Try this:

Please Login or Register  to view this content.

**Haluk** · 07-19-2020, 05:12 PM

Another alternative is using "Regular Expressions" in a UDF which should be entered as an array function (i.e. Ctrl+Shift+Enter) to get before and after ZIP code.

Please Login or Register  to view this content.

Sample workbook is also attached...

**nordicdust** · 07-19-2020, 05:13 PM

Thank you TMS

It works perfect.

Now I can go to ed without thinking of this :-)
Tomorrow I wil analyze your code to understand it and learn :-)

**nordicdust** · 07-19-2020, 05:18 PM

Thank you Haluk

Very nice

I had looked in regex but it was way past my skils as a vba beginner :-)

But I can se that it is a prefered solution for finding and extracting patens.

**TMS** · 07-19-2020, 05:18 PM

You're welcome.

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.

**Haluk** · 07-19-2020, 05:21 PM

You're welcome...

Yes, RegEx is an useful tool for this type of problems.

**TMS** · 07-19-2020, 05:28 PM

@Haluk: I love to see Regular Expressions ... I just have no idea how they work

. Nice solution and avoids all the intermediate stages.

The only issue is that the function doesn't extract anything if there is a ZIP code.

**Haluk** · 07-19-2020, 05:34 PM

@TMS;

It's nice to see a RegEx fan

and thx for the rep.

Actually I was aware about the fact you mentioned ...

But its somewhat late in Turkey now. Tomorow I'll look at it again.

**TMS** · 07-19-2020, 05:51 PM

I would think ...

Please Login or Register  to view this content.

**Rick Rothstein** · 07-19-2020, 06:05 PM

I believe this non-RegExp macro will also work...

Please Login or Register  to view this content.

SIDE NOTE: I used to write Regular Expression scripts on a UNIX mini-computer back in the 1990's, but haven't touched it since. I do remember saying back then that Regular Expressions is one of few languages where you could write something that you could not figure out what it did when reading it back a half-hour after having written it.

**Haluk** · 07-20-2020, 02:59 AM

@TMS;

Something like this may work:

Please Login or Register  to view this content.

**Rick Rothstein** · 07-20-2020, 03:51 AM

I am not sure why a UDF solution is being pushed instead of a macro solution. I do not see the advantage of loading up Columns C and D with array-entered UDF formulas... it seems having a macro place constants in those cells would be more efficient overall. That is why I posted the non-RegExp macro that I did in Message #11. However, if an array-entered UDF turns out to be what the OP ends up wanting, here is such non-RegExp for the OP to consider...

Please Login or Register  to view this content.

**Haluk** · 07-20-2020, 04:05 AM

@Rick Rothstein;

UDF solution is not being pushed and no one is being forced to use it..... as you can see in my first message (#3) I offered just an alternative solution with RegEx.

The rest of the messages continued with advantages and disadvantages of RegEx.

Note: The RegEx function can also be compiled as a "Sub" procedure as you know.

**Rick Rothstein** · 07-20-2020, 04:14 AM

Sorry, "pushed" was a bad choice of words. I still think a macro solution would be more efficient overall. You should consider recasting your UDF into a macro in case the OP wants to go with a RegExp solution rather than a non-RegExp solution just so he has the choice.

**Haluk** · 07-20-2020, 05:05 AM

So; if the OP wants to use the RegEx solution provided in message #12 in a Sub procedure, one alternative is;

Please Login or Register  to view this content.

**nordicdust** · 07-20-2020, 05:58 AM

Thanks Rick
Nice work, It is perfekt.

**Bo_Ry** · 07-20-2020, 06:54 AM

Formula solution
F2
=TRIM(SUBSTITUTE(RIGHT(SUBSTITUTE(B2,"\",REPT(" ",99)),99),G2,))

G2
=IFNA(TRIM(MID(SUBSTITUTE(" "&B2," ",REPT(" ",200)),MATCH(TRUE,INDEX(--TEXT(FILTERXML("<b><a>"&SUBSTITUTE(B2," ","</a><a>")&"</a></b>","//a"),"0;;;\0")>999,),)*200,6^6)),"")

Split before Zip Code

LinkBack

Thread Tools

Rate This Thread

Display

Split before Zip Code

Re: Split before Zip Code

Re: Split before Zip Code

Re: Split before Zip Code

Re: Split before Zip Code

Re: Split before Zip Code

Re: Split before Zip Code

Re: Split before Zip Code

Re: Split before Zip Code

Re: Split before Zip Code

Re: Split before Zip Code

Re: Split before Zip Code

Re: Split before Zip Code

Re: Split before Zip Code

Re: Split before Zip Code

Re: Split before Zip Code

Re: Split before Zip Code

Re: Split before Zip Code

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Code to split

need vba code to split rows to closed wb shets depends company code met with sht name

Need Macro code to split data split in 7 sheets based on variable rows in column A

[SOLVED] How to Split VBA code in New lines

[SOLVED] VB Code to split data

how to convert excel split code to word split code

Split code in more than one page ?

Bookmarks

Bookmarks

Posting Permissions