Remove Specific Text - HELP!

[LastHair]

*Deeep breath*

Hello,

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

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

On to the problem.

I am a marketing manager and I just started my job. The company I a
working for is extremely disorganized. Hopefully I can fix that (a
least the marketing part). They have handed me a list in a text fil
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 wa
Excel. The next thing I thought of is how to make this informatio
useful. Having done mail-merges at my old job I knew that I woul
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.
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
constant 3 rows for each address. Well I would have had just a secon
cup of coffee, not a third, forth and n'th. The data, some 25,00
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 line
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 ZI
and number rows together at the top and the Company Name and Addres
well below it. The first column numbers are of course now out o
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 the
sorted the information. At the upper part were the addresses th
bottom the names. Since the sort puts numbers above letters. Look
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 T
the City, St Zip Phone number. They lined up perfectly. So i
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 jus
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


+-------------------------------------------------------------------+
|Filename: Split Addresses.zip |
|Download: http://www.excelforum.com/attachment.php?postid=5048 |
+-------------------------------------------------------------------+

 

[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

 

[LastHair]

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

It was actually only a small problem. I was able to filter out th
COMPANY NAMES from the Address and correct the uppercase lowercas
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 m
thus far

 

[Dav]

If your addresses become all captials, the logic I gave you will fail
as this was how I dentified the first line of the addresses uniquely

Regards

Da

 

[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!! =)