Text to column with uneven delimiters and spacing

[beginnergeek]

Hi all!

Trying to figure out a formula to create a text-to-column result with address data, my issue is that some of the entries have an address line 2 data, as well as some, have an extra comma in the name which makes it difficult to use a simple text to column with the comma serving as a delimiter.
Any help would be greatly appreciated!

Book2
	A
1	RITE CHOICE REALTY LLC, 436 SOUTH AVE WEST, WESTFIELD,NJ 07090
2	NJ CONSULTANTS INC , 2ND FLOOR, 525 PIAGET AVE RT 46 EAST, CLIFTON,NJ 07011
3	WATCHUNG HILLS REALTY GROUP LLC, 60 MOUNTAIN BLVD, WARREN,NJ 07059
4	ALLIANCE REALTY OF NEW JERSEY LLC, 704 E. MAIN STREET, SUITE K, MOORESTOWN,NJ 08057
5	NRK OF NEW JERSEY INC , 457 SYLVAN AVENUE, 2ND FLOOR, ENGLEWOOD CLIFFS,NJ 07632
6	PETER COSTANZO AUCTIONEERS INC , 22 SMOCK ST, NEPTUNE CITY,NJ 07753
7	RED DOOR REALTY GROUP INC., 34 N MAIN ST , MARLBORO,NJ 07746
8	WEICHERT CO , 1625 ROUTE 10 EAST, MORRIS PLAINS,NJ 07950
9	COLDWELL BANKER REAL ESTATE SERVICES LLC, 175 PARK AVE, MADISON,NJ 07940
10	ENCORE REALTY GROUP, LLC, 102 TOWN CENTER DRIVE, WARREN,NJ 07059
11	COBO REALTY INC., 181 NORTH AVENUE EAST, CRANFORD,NJ 07016
12	G & G AGENCY INC , 236 NORWOOD & ROOSEVELT AVENUE, P O BOX 409, DEAL,NJ 07723
Sheet1

Book2
	A	B	C	D	E
1	RITE CHOICE REALTY LLC	436 SOUTH AVE WEST	WESTFIELD	NJ 07090
2	NJ CONSULTANTS INC	2ND FLOOR	525 PIAGET AVE RT 46 EAST	CLIFTON	NJ 07011
3	WATCHUNG HILLS REALTY GROUP LLC	60 MOUNTAIN BLVD	WARREN	NJ 07059
4	ALLIANCE REALTY OF NEW JERSEY LLC	704 E. MAIN STREET	SUITE K	MOORESTOWN	NJ 08057
5	NRK OF NEW JERSEY INC	457 SYLVAN AVENUE	2ND FLOOR	ENGLEWOOD CLIFFS	NJ 07632
6	PETER COSTANZO AUCTIONEERS INC	22 SMOCK ST	NEPTUNE CITY	NJ 07753
7	RED DOOR REALTY GROUP INC.	34 N MAIN ST	MARLBORO	NJ 07746
8	WEICHERT CO	1625 ROUTE 10 EAST	MORRIS PLAINS	NJ 07950
9	COLDWELL BANKER REAL ESTATE SERVICES LLC	175 PARK AVE	MADISON	NJ 07940
10	ENCORE REALTY GROUP	LLC	102 TOWN CENTER DRIVE	WARREN	NJ 07059
11	COBO REALTY INC.	181 NORTH AVENUE EAST	CRANFORD	NJ 07016
12	G & G AGENCY INC	236 NORWOOD & ROOSEVELT AVENUE	P O BOX 409	DEAL	NJ 07723
Sheet1

 

[benlinford]

A couple of things;

• You mentioned two types of exceptions that you want to account for but didn't provide any examples of those... (?)
• Going forward, if you are anticipating running this process on future new data, is there an opportunity for you to more adequately control user input?
  (this will prevent you needing to run this process on the data in the future or at least minimise the risk that some records are slipping through undetected at your exception checking logic layer and causing further problems)

 

[beginnergeek]

Sorry about that.

Issue num 1
would be in row 2

2
NJ CONSULTANTS INC , 2ND FLOOR, 525 PIAGET AVE RT 46 EAST, CLIFTON,NJ 07011

Or row 5

5
NRK OF NEW JERSEY INC , 457 SYLVAN AVENUE, 2ND FLOOR, ENGLEWOOD CLIFFS,NJ 07632

Where there is an additional comma in the address data
This would result in the following using text-to-column with a comma delimiter

NJ CONSULTANTS INC

2ND FLOOR

525 PIAGET AVE RT 46 EAST

CLIFTON

NJ 07011

NRK OF NEW JERSEY INC

457 SYLVAN AVENUE

2ND FLOOR

ENGLEWOOD CLIFFS

NJ 07632

Issue num 2
would be in row 10

10
ENCORE REALTY GROUP, LLC, 102 TOWN CENTER DRIVE, WARREN,NJ 07059

Where there is an additional comma in the company name,
This would result in the following using a text-to-column with a comma as the delimiter

ENCORE REALTY GROUP

LLC

102 TOWN CENTER DRIVE

WARREN

NJ 07059

The data is scrapped using public records, and don't really have the ability to more adequately control input on future data.

Thanks

 

[kweaver]

See if this gets you anywhere.

With a helper column in C, I used: =SUBSTITUTE(A1,", LLC", " LLC") (original data in column A).
(might want to do something similar with INC)
Then, in column D, I used: =IF(4=LEN(C1)-LEN(SUBSTITUTE(A1,",","")),SUBSTITUTE(C1,",","",2),C1)
Then, I copied the data in D and did a paste special/values in column E
Then, did a text to columns using E and a , delim and got this:

RITE CHOICE REALTY LLC	436 SOUTH AVE WEST	WESTFIELD	NJ 07090
NJ CONSULTANTS INC	2ND FLOOR 525 PIAGET AVE RT 46 EAST	CLIFTON	NJ 07011
WATCHUNG HILLS REALTY GROUP LLC	60 MOUNTAIN BLVD	WARREN	NJ 07059
ALLIANCE REALTY OF NEW JERSEY LLC	704 E. MAIN STREET SUITE K	MOORESTOWN	NJ 08057
NRK OF NEW JERSEY INC	457 SYLVAN AVENUE 2ND FLOOR	ENGLEWOOD CLIFFS	NJ 07632
PETER COSTANZO AUCTIONEERS INC	22 SMOCK ST	NEPTUNE CITY	NJ 07753
RED DOOR REALTY GROUP INC.	34 N MAIN ST	MARLBORO	NJ 07746
WEICHERT CO	1625 ROUTE 10 EAST	MORRIS PLAINS	NJ 07950
COLDWELL BANKER REAL ESTATE SERVICES LLC	175 PARK AVE	MADISON	NJ 07940
ENCORE REALTY GROUP LLC	102 TOWN CENTER DRIVE	WARREN	NJ 07059
COBO REALTY INC.	181 NORTH AVENUE EAST	CRANFORD	NJ 07016
G & G AGENCY INC	236 NORWOOD & ROOSEVELT AVENUE P O BOX 409	DEAL	NJ 07723