Parsing out text

Hi,

I have a column of alpha-numeric strings with an address in the middle as follows:

John Doe 06 CH 1837 123 Any St, Any Town, IL 60612 Pierce & Associates

The red text (the address) is what I want to cut from this string and move to an adjacent column.

The string starts with a name of any length, but I know that every string has the blue text that always starts with CH and then is followed by a space and four integers as shown above. The numbers change but the CH is constant.

So I know that I need to search each line for CH, move to the right 5 spaces past that (to the beginning of the address), and cut everything up until the end of the next 5 integers (the zip code), then move it to the next column. And then some sort of loop applied.

I know what I need done I just don't know how to write the macro. Can anyone please help. Many thanks in advance.

EDIT: By the way the text in the workbook is not colored red or blue, only here for purpose of illustration.

Ryan

Thanks a bunch mudraker,

Everything works pretty good including the options you put in. The only thing is that for some reason about 10% of the lines are truncated or have additional text past the zip code. I don't see any commonality between the strings that have this problem.

Here are a couple examples where the red is the output column:

Kiril Krkolev 06 CH 1729 150 Enclave Cricle Unit C, Bolingbrook,IL 60440 Codilis & Associates 150 Enclave Cricle Unit C, Bolingbr

Aida R Dismaya 06 CH 1604 2282 Twilight Drive, Aurora, IL 60504 Weltman, Weinberg & Reis 2282 Twilight Drive, Aurora, IL 60504 Weltman, Weinberg

Matthew Larkins 06 CH 1692 2823 Sheffield, New Lenox, IL 60451 Hauselman, Rappin & Olswang 2823 Sheffield, New Lenox, IL 60451 Hauselman, Rappin &

Any ideas?

Ryan