Separating a string of data from one cell into separate cells

[Sheila3434]

I need to separate my data into separate columns for mailing purposes. I pull my data from an outside data source and it is formatted into 2 columns: Name and Address. It needs to be separated into the following columns: First, Last, Address, City, State, and Zip Code, for another department to generate mailing labels.

My current first column is Last name, First name, Credentials (MD/DO etc). The next column is Street address City, ST, Zip. (Please note there is no punctuation between the street address and the city. This is the way it is pulled from our outside source. Possibly some addresses may be listed as 123 15th St or 12987 New York Avenue or 6875 Eagleview. There is not a consistent format in the addresses.)

The names must be reflected as First Name Last Name, MD. Since the first name is currently between the last name and initials, I am stuck. I am also stuck as to how to differentiate the street address and the city.

I have attached my worksheet with these scenarios. Thank you so much for your help.

Sheila

[alansidman]

Using Power Query, I have been able to address all the issues except for separating the City from the address. Look at the attached. Hopefully someone with a little bit more skill can figure that part out.

Here is the MCode for that

Please Login or Register  to view this content.

[Richard Buttrey]

The difficult bit is the Address and City since the number of words in each field will vary. The other fields are easy.

If the data is coming from an outside source why isn't it possible to output separate fields.
Most back office systems can oputput csv files. I'd be inclined to look a bit more into getting the right information in the first place rather than trying to split strings as you're attempting here.

Uisng a test row 9
The First Name:

Please Login or Register  to view this content.

Last Name:

Please Login or Register  to view this content.

State:

Please Login or Register  to view this content.

Zip:

Please Login or Register  to view this content.

[Sheila3434]

Wow, I have no idea what to do with this. I was just looking for formulas! I have never done anything Power Query. There is another girl in my office who might understand this. We will see what we can do with with this.

As for the city/address issue, once we have gotten it this far, could we not just search the cell from the right for the space (which should be the city, unless it is two words. We could just do a visual check to see if there are any two word cities and manually fix these.)

Thanks for your help.

[Sheila3434]

It might be possible; but this outside source does not like to make changes like this. Also, we need this fairly quick (like today).

[alansidman]

Open Power Query/Get and Transform. Click on New Query.

Open blank query in the editor, launch Advanced Editor and paste in the code I gave you above. Make sure that your raw data is named Table1.

[Richard Buttrey]

OK, in addition to my previous formulae

Address:

Please Login or Register  to view this content.

City:

Please Login or Register  to view this content.

You'll need to eyeball the city results where a City comprises two words.

[Sheila3434]

Thank you, guys for your help. You all are AWESOME!!!! I am so glad I found this site. I use formulas quite a bit; but sometimes I need nudges or total help with something. My mind sometimes just wants to sleep and will not think the steps clearly. This site has helped me so many times; even reading other posts and seeing the answers given.

Have a great day!