How do I parse a cell that contains the company name and address.

[DonnaS]

How do I parse a cell that contains the company name and address? The previous cell has the company name if that helps?

[TABLE="width: 814"]
<tbody>[TR]
[TD]Customer[/TD]
[TD]Ship to[/TD]
[/TR]
[TR]
[TD]100% KIDS[/TD]
[TD]100% KIDS 485 CENTRAL AVE CEDARHURST, NY 11516[/TD]
[/TR]
[TR]
[TD]24 HOUR FITNESS[/TD]
[TD]UPS-SCS: 24 HOUR FITNESS 11991 LANDON DR MIRA LOMA, CA 91752[/TD]
[/TR]
[TR]
[TD]2 HOT YOGA[/TD]
[TD]1808 Old Briar Rd Highland Park, IL 60035[/TD]
[/TR]
[TR]
[TD]432 WEST SIDE[/TD]
[TD]432 WEST SIDE BELLEAIR BLUFFS PLAZA 100 INDIAN ROCK ROAD BEL[/TD]
[/TR]
[TR]
[TD]4 KIDS ONLY - TX[/TD]
[TD]4 KIDS ONLY 2900 W WASHINGTON, #120 STEPHENVILLE, TX 76401[/TD]
[/TR]
[TR]
[TD]4 SISTERS 1 CLOSET[/TD]
[TD]7080 nw 4th St PLANTATION, FL 33317[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Gerald Higgins]

Hi, welcome to the board.

This is a very common type of question on this board, and it's not easy unless you can find a consistent pattern in your data.

Is there a pattern in your data ?

I can't see a consistent pattern in what you've posted.

And I have no idea where "2 HOT YOGA" and "4 SISTERS 1 CLOSET" fit in.

 

[DonnaS]

This is the company name. When the data is downloaded it puts the company name in one cell and the company name again + the address into the second cell.

I would like to separate by the below headings if possible:
CompanyName|Address|City|State|ZipCode

 

[Gerald Higgins]

What you said in post #3 doesn't seem to match what you said in post #1.

"24 HOUR FITNESS" is just one of several examples in 6 lines of data that DON'T match what you said in post#3.

 

[DonnaS]

Sorry to clarify there is no specific format. The two examples you were referring to were ones that had company names in front start the address field. Of the 1000+ addresses in need to parse 85% have the company name in the address feild.

 

[Gerald Higgins]

OK. When you say "parse", what exactly do you want to do ?

 

[DonnaS]

To separate it into fields so that I can upload into my system.

Address|City|State|ZipCode

 

[Gerald Higgins]

OK.
So let's think about this.
Forgetting about Excel specifically for a moment . . . taking your sample data, how exactly would YOU instruct someone else to parse out the company name ?

 

[DonnaS]

Is there a way to delete phrase equal to contents in a cell? This would remove everything but the address and then a seperate function to seperate the address?

 

[Gerald Higgins]

You can indeed delete a phrase equal to the contents of a cell.

Let's take one of your examples, and make some guesses about what cells they are in.

Cell A1....................Cell B1
432 WEST SIDE.......432 WEST SIDE BELLEAIR BLUFFS PLAZA 100 INDIAN ROCK ROAD BEL

=substitute(B1,A1,"")

Try this and see what you get.

This should also work for SOME of the other examples you gave, but not all of them.

[TABLE="class: cms_table, width: 814"]
<tbody>[TR]
[TD]100% KIDS[/TD]
[TD]Should work[/TD]
[/TR]
[TR]
[TD]24 HOUR FITNESS[/TD]
[TD]Should work[/TD]
[/TR]
[TR]
[TD]2 HOT YOGA[/TD]
[TD]Won't work[/TD]
[/TR]
[TR]
[TD]432 WEST SIDE[/TD]
[TD]Should work[/TD]
[/TR]
[TR]
[TD]4 KIDS ONLY - TX[/TD]
[TD]Won't work -it's close but not an exact match[/TD]
[/TR]
[TR]
[TD]4 SISTERS 1 CLOSET[/TD]
[TD]Won't work[/TD]
[/TR]
</tbody>[/TABLE]