Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Remove Specific Text - HELP!
*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. :) -- LastHair ------------------------------------------------------------------------ LastHair's Profile: http://www.excelforum.com/member.php...o&userid=36467 View this thread: http://www.excelforum.com/showthread...hreadid=562319 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Remove Specific Text - HELP!
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 -- VBA Noob ------------------------------------------------------------------------ VBA Noob's Profile: http://www.excelforum.com/member.php...o&userid=33833 View this thread: http://www.excelforum.com/showthread...hreadid=562319 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Remove Specific Text - HELP!
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,MATC H(E1,$A$1:$A$400,0),0),"") in H1 put =IF(G1="",OFFSET($B$1,MATCH(E1,$A$1:$A$400,0),0),O FFSET($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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Remove Specific Text - HELP!
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" wrote in message ... 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,MATC H(E1,$A$1:$A$400,0),0),"") in H1 put =IF(G1="",OFFSET($B$1,MATCH(E1,$A$1:$A$400,0),0),O FFSET($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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Remove Specific Text - HELP!
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. -- LastHair ------------------------------------------------------------------------ LastHair's Profile: http://www.excelforum.com/member.php...o&userid=36467 View this thread: http://www.excelforum.com/showthread...hreadid=562319 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Remove Specific Text - HELP!
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 Dav -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=562319 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Remove Specific Text - HELP!
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!! =) -- LastHair ------------------------------------------------------------------------ LastHair's Profile: http://www.excelforum.com/member.php...o&userid=36467 View this thread: http://www.excelforum.com/showthread...hreadid=562319 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Filter text in a column by its Indent, to remove certain text | Excel Discussion (Misc queries) | |||
How do I search for specific text and sum the cell to the right? | Excel Worksheet Functions | |||
can you find specific text in a string ignoring any other text | Excel Discussion (Misc queries) | |||
Using 'If' refer to specific words in a cell containing text | Excel Worksheet Functions | |||
How do I remove all text to the left of the @ in an email address | Excel Worksheet Functions |