Macro to remove all text to the right of numbers.

[Trouble085]

Hi, I have data in the following formats;

"near27A the esplanade bagara"

<COLGROUP><COL style="WIDTH: 296pt; mso-width-source: userset; mso-width-alt: 14409" width=394><TBODY>
</TBODY>

"op10 TANNER ST BARGARA"

<COLGROUP><COL style="WIDTH: 296pt; mso-width-source: userset; mso-width-alt: 14409" width=394><TBODY>
</TBODY>

"130 MILLER ST BARGARA" "sub2549 22RIFEL RAnGE RD innes park nor" "abs3971cnr moodies rd/woongara scenic dr" What i need is to have all the street names in one column, all the street numbers in one column and all other information in another. For example; "near27A the esplanade bagara" would be, A1 = "Near", B1 = "27A" and C1 = "the esplanade bagara" Even if all I can do is remove all text to the right of the numbers that will get me started. Regards Tim <TBODY> </TBODY><COLGROUP><COL></COLGROUP>

<COLGROUP><COL style="WIDTH: 296pt; mso-width-source: userset; mso-width-alt: 14409" width=394><TBODY>
</TBODY>

<COLGROUP><COL style="WIDTH: 296pt; mso-width-source: userset; mso-width-alt: 14409" width=394><TBODY>
</TBODY>

 

[Tom Urtis]

That's an ugly set of data you have there, which I'm sure you were already aware and I am sure you inherited it so it's not your fault, but I couldn't resist. For example, in your example, 27A is what you want to identify, but the "A" of that *should* be included though not a number, whereas in other cases a consonant or non-number would not be included, such as 27 A Street or your other example of cnr with abs3971cnr. Yeesh. Maybe if you suggest some rules of thumb with regards to when a non-number should be assumed as part of a number and when it should not be, that can be a start. Also, how many rows are there, are they separated by empty rows to identify associated blocks of data, and so on.

 

[Trouble085]

That's an ugly set of data you have there, which I'm sure you were already aware and I am sure you inherited it so it's not your fault, but I couldn't resist. For example, in your example, 27A is what you want to identify, but the "A" of that *should* be included though not a number, whereas in other cases a consonant or non-number would not be included, such as 27 A Street or your other example of cnr with abs3971cnr. Yeesh. Maybe if you suggest some rules of thumb with regards to when a non-number should be assumed as part of a number and when it should not be, that can be a start. Also, how many rows are there, are they separated by empty rows to identify associated blocks of data, and so on.

Yeah, I am fully aware of how poor the data is and can assure you I AM NOT the person who created it!! The problem is there are no real "Rules of thumb" as someone enters the data via a PDA in the field (something I have no control over and no influence in). Like i said if it only removed everything after the last integer that would be at least som data I could work with.

Thanks for your reply.

 

[Trouble085]

Some standard terms used that are not a part of the address are "Sub1234", "ABS1234" , "Links1234", "lks1234".
I do have a basic understanding of VBA and if someone could provide an example i should be able to work from there....i think.

 

[Tom Urtis]

Well, for starters, try this...

Suppose in cell A1 you have, as you posted, this item:
near27A the esplanade bagara

In cell C1 enter this array formula (Ctrl+Shift+Enter, thanks Fairwinds):
=MID(A1,MATCH(FALSE,ISERROR(1*MID(A1,ROW(INDIRECT("1:255")),1)),0),255-SUM(1*ISERROR(1*MID(A1,ROW(INDIRECT("1:255")),1))))*1

In cell B1 enter
=LEFT(A1,FIND(C1,A1)-1)

In cell D1 enter
=MID(A1,FIND(C1,A1)+LEN(C1),LEN(A1))

Now in this narrow example, it's true that the A will be attached to the returned value in cell D1 but that gets back to some of the rules of thumb that might be employed if this approach gets you started.

 

[Trouble085]

Sorry, I didn't answer the all the questions.
A non-number should be assumed as part of a number when it is before the Number or it is a single letter followed by a space directly after the number ("1234A StreetName").
There are a vairing number of rows but never more than 1000.
There are no blank rows and no blank cells in the column this data is in.

 

[Tom Urtis]

Thanks. We may have crossed posts...see if the formula approach I posted above is helpful in any way.

 

[Trouble085]

I did as below but it is only showing an error in all cells with formulas.
I am using Excel 2003 at this point in time if that helps?

I really appricaite your time, Thanks.

Well, for starters, try this...

Suppose in cell A1 you have, as you posted, this item:
near27A the esplanade bagara

In cell C1 enter this array formula (Ctrl+Shift+Enter, thanks Fairwinds):
=MID(A1,MATCH(FALSE,ISERROR(1*MID(A1,ROW(INDIRECT("1:255")),1)),0),255-SUM(1*ISERROR(1*MID(A1,ROW(INDIRECT("1:255")),1))))*1

In cell B1 enter
=LEFT(A1,FIND(C1,A1)-1)

In cell D1 enter
=MID(A1,FIND(C1,A1)+LEN(C1),LEN(A1))

Now in this narrow example, it's true that the A will be attached to the returned value in cell D1 but that gets back to some of the rules of thumb that might be employed if this approach gets you started.

 

[Trouble085]

Okay, that does work. Sorry, forgot Ctrl+Shift+Enter.

I'll see what I can do with that thanks heaps!!