Remove Specific Text - HELP!

[LastHair]

*Deeep breath*

Hello,

This is the writings of a person that is just about to go nuts. If you are reading this than I thank you for being with me right before I go crazy. I have run in what seems like a simple problem, but in fact I think is probably just about impossible to solve.

Just for the Record: I have been trying to solve this problem for what I think now is 3 weeks. I have done (and read) everything I could fathom but nothing.

On to the problem.

I am a marketing manager and I just started my job. The company I am working for is extremely disorganized. Hopefully I can fix that (at least the marketing part). They have handed me a list in a text file of customers.

The text format is like this.

Company Name
123 Street Name
City, ST 98765 (123) 456-7890

So they asked me to organize it. The first think I thought of was Excel. The next thing I thought of is how to make this information useful. Having done mail-merges at my old job I knew that I would need to break up the information in several intelligent columns. Something like

Name | Address | City | ST | ZIP | Phone | Extra Stuff | More Stuff

Ahhh so far so good. I'm looking at the clock and its 8:30am. I thinking I could have this done by 9am and get a second cup of coffee. Mind you I'm no excel expert, but it seems pretty straight forward. That was 3 weeks ago.

So whats the problem?

The problem is as such. If the information given to me was in a constant 3 rows for each address. Well I would have had just a second cup of coffee, not a third, forth and n'th. The data, some 25,000 entries is not consistent. So what do I find in the text file.

ACME CORP. (always cap.)
123 Acme Road
Johnsonville, WI 12345 (123) 456-7890

FUNNYBUNNY INC
Merryville, WI 12345 (098) 765-4321


Ohhh the pain....

So I didnt give up and have made progress.

FIRST: I took all the entries and imported them into excel like this.

ColA | ColB | ColC

ACME CORP.
123 Acme Road
Johnsonville | WI 12345 | 123) 456-7890

SECOND: I inserted a column on the left and numbered all the lines sequentially. Sort of like this:

1 | ACME CORP.
2 | 123 Acme Road
3 | Johnsonville | WI 12345 | 123) 456-7890
4 | ACME CORP.
5 | 123 Acme Road
6 | Johnsonville | WI 12345 | 123) 456-7890

Third: I sorted by Phone Number. This grouped all the City, State ZIP and number rows together at the top and the Company Name and Address well below it. The first column numbers are of course now out of order. I kept them there as reference. Thought I might need them. Looked like this

10 | Johnsonville | WI 12345 | 123) 456-7890
97 | Johnsonville | WI 12345 | 123) 456-7890
61 | Johnsonville | WI 12345 | 123) 456-7890
08 | ACME CORP.
09 | 123 Acme Road
95 | ACME CORP.
96 | 123 Acme Road
59 | ACME CORP.
58 | 123 Acme Road

FORTH: I copied the NAME and Address part to another Sheet. I then sorted the information. At the upper part were the addresses the bottom the names. Since the sort puts numbers above letters. Looks like this: (Second Sheet, not the first)

09 | 123 Acme Road
96 | 123 Acme Road
58 | 123 Acme Road
08 | ACME CORP.
95 | ACME CORP.
59 | ACME CORP.

So far so good =)

FIFTH: I copied the names from the Second Sheet to a column NEXT TO the City, St Zip Phone number. They lined up perfectly. So it looked like this.

10 | ACME CORP. | Johnsonville | WI 12345 | 123) 456-7890
97 | ACME CORP. | Johnsonville | WI 12345 | 123) 456-7890
61 | ACME CORP. | Johnsonville | WI 12345 | 123) 456-7890

*breath* Ahhhhh... so far so good. I have 25,000 NAMES and 25,000 City, ST Zip Phone entries.

All I have to do now is just bring over the addresses. Oh...Ohh....Ughh!!! I only have 24,723. WHAT THE HECK?!?!? Welll thats where my problem is. A lot of these entries were just created but no actual address was ever put in.

Why you ask?? Get in line, I've been asking it for about...well the last 3 weeks now.

_____________________________________________________________

By the way, if you are still reading this far, you are a good person...and you may not believe me....but Thank You.

_____________________________________________________________

But maybe something can save me. The numbers i put in at the beginning, they still line up....Hmmm. So I copy/paste in JUST the Address under the NAME, City, Zip Phone. Looked like this.

10 | ACME CORP. | Johnsonville | WI 12345 | 123) 456-7890
97 | ACME CORP. | Johnsonville | WI 12345 | 123) 456-7890
61 | ACME CORP. | Johnsonville | WI 12345 | 123) 456-7890
09 | 123 Acme Road
96 | 123 Acme Road
58 | 123 Acme Road

I then Sort by ColA and get something that looks like this.

09 | 123 Acme Road
10 | ACME CORP. | Johnsonville | WI 12345 | 123) 456-7890
97 | ACME CORP. | Johnsonville | WI 12345 | 123) 456-7890
58 | 123 Acme Road
61 | ACME CORP. | Johnsonville | WI 12345 | 123) 456-7890

Looking at this really close you will see that I omitted line 96. I did it to illustrate the problem I have. Not every Customer Entry has an address field. Most do, but the few that dont, keep me from creating an intelligent layout.

So I've decided to just scrap the lines that do not have a subsequent address . I'm mean I dont even really need them.

So here is my question.

Looking at the sample above is there a way to instruct Excel, or any other program for that matter to only keep the lines that have a subsequent address?

NOTE: I know this problem is a bit confusing and I'm not certain my explanation is the best. The idea is I just want to remove the lines that dont have an address attached to them. I want my entire file to look like this.

ACME CORP. | Johnsonville | WI 12345 | 123) 456-7890
123 Acme Road
ACME CORP. | Johnsonville | WI 12345 | 123) 456-7890
123 Acme Road
ACME CORP. | Johnsonville | WI 12345 | 123) 456-7890
123 Acme Road

AND eventually like this

1. ACME CORP. | 123 Acme Road | Johnsonville | WI 12345 | 123) 456-7890

2.ACME CORP. | 123 Acme Road | Johnsonville | WI 12345 | 123) 456-7890

3.ACME CORP. | 123 Acme Road | Johnsonville | WI 12345 | 123) 456-7890


PAAALLEEEEEEEEEEEASE HELP ME!!! Honestly I appreciate it if you've read this far.

[VBA Noob]

Didn't get far myself

You could identify the Company names with this forumla if all in upper case by putting this in say C2

=IF(A1<>"",EXACT(A1,UPPER(A1)))

Then Filter on the True's and add this forumlua to D2

=IF(AND(C1=TRUE,OFFSET(C1,1,0)=FALSE,OFFSET(C1,2,0)=FALSE),"Two lines","one Line")

Then in E2 enter

=COUNTIF($C$1:C1,"TRUE") to group the addresses e.g

Company Name 1
Address Line 1 1
Address Line 2 1
Company Name 2
Address Line 1 3
Company Name 3
Address Line 1 3

Then you could stick it in the pivot and only look at Uncomplete addresses ??

Which should help

VBA Noob

[Dav]

From the original data I would try something like the attached and if it works then split the last line of the address

Insert a column to the left
in A1 put =IF(B1=UPPER(B1),1,0)
in A2 put =IF(ISERROR(FIND(B2,UPPER(B2))),A1,A1+1) and copy it down to the end of your data

In a free column my example column E put a 1 in E1, 2 in E2 and copy down so you get sequential numbers
in F1 put =VLOOKUP(E1,$A$1:$B$4000,2,0)

in G1 put =IF(OFFSET($A$1,MATCH(E1,$A$1:$A$400,0),0)=OFFSET($A$1,MATCH(E1,$A$1:$A$400,0)+1,0),OFFSET($B$1,MATCH(E1,$A$1:$A$400,0),0),"")

in H1 put =IF(G1="",OFFSET($B$1,MATCH(E1,$A$1:$A$400,0),0),OFFSET($B$1,MATCH(E1,$A$1:$A$400,0)+1,0))

these can then be copied down for as many rows as you need

If they work I would copy them as values and delete the formula

then use the left, right mid functions with search and len and find to split the data in column H into the appropraite Fields

Regards

Dav

[CIL]

I had to try this and I am getting the info for the 1st address in column b
repeating every other row in f-h.
The second and third address is not being entered into columns f-g.
cil
"Dav" <[email protected]> wrote in message
news:[email protected]...
>
> From the original data I would try something like the attached and if it
> works then split the last line of the address
>
> Insert a column to the left
> in A1 put =IF(B1=UPPER(B1),1,0)
> in A2 put =IF(ISERROR(FIND(B2,UPPER(B2))),A1,A1+1) and copy it down to
> the end of your data
>
> In a free column my example column E put a 1 in E1, 2 in E2 and copy
> down so you get sequential numbers
> in F1 put =VLOOKUP(E1,$A$1:$B$4000,2,0)
>
> in G1 put
> =IF(OFFSET($A$1,MATCH(E1,$A$1:$A$400,0),0)=OFFSET($A$1,MATCH(E1,$A$1:$A$400,0)+1,0),OFFSET($B$1,MATCH(E1,$A$1:$A$400,0),0),"")
>
> in H1 put
> =IF(G1="",OFFSET($B$1,MATCH(E1,$A$1:$A$400,0),0),OFFSET($B$1,MATCH(E1,$A$1:$A$400,0)+1,0))
>
> these can then be copied down for as many rows as you need
>
> If they work I would copy them as values and delete the formula
>
> then use the left, right mid functions with search and len and find to
> split the data in column H into the appropraite Fields
>
> Regards
>
> Dav
>
>
> +-------------------------------------------------------------------+
> |Filename: Split Addresses.zip |
> |Download: http://www.excelforum.com/attachment.php?postid=5048 |
> +-------------------------------------------------------------------+
>
> --
> Dav
> ------------------------------------------------------------------------
> Dav's Profile:
> http://www.excelforum.com/member.php...o&userid=27107
> View this thread: http://www.excelforum.com/showthread...hreadid=562319
>

[LastHair]

So far so good. All the suggestions have been really great. I did hit a bit of a wall though. As it turns out not all the COMPANY NAMES are capitalized. Most are, but not all. Also, as it turns out not all the Addresses are in capital type.

It was actually only a small problem. I was able to filter out the COMPANY NAMES from the Address and correct the uppercase lowercase problem and make them all consistant.

Now to put them back into the sheet. Hope it works =)

By the way, thank you to everyone that took the time out to help me thus far.

[Dav]

If your addresses become all captials, the logic I gave you will fail, as this was how I identified the first line of the records uniquely. Unless you do this after you have split the addresses


Regards

Dav

[LastHair]

Hi Dav,

Sorry I didnt explain it well. From the suggested solutions I would need my addresses to be formated as such.

COMPANY NAME (all upper case)
123 Address (all capital case, meaning first letter is upper case and rest are lower case)
City, ST 12345 132-456-7890 (all capital case)

This waychecking for an all uppper case line will establish it as the COMPANY NAME and everthing that follows is the address and so on.

Well my problem was that my addresses did not necessarily follow this format, but now was able to fix them so they do now. So now I can use the suggested solutions. It wont be easy, but THANK GOD IT WORKS!! =)