Convert a two line address to individual columns?

[ECJ53]

I have a spreadsheet that has 6 columns for Company, Address, Phone Number, etc.

The address column is actually taking two rows (i.e Street Address on top row, and City, State, Zip on bottom row).

Is it possible to extract/convert those two rows into individual columns for Address, City, State, Zip and maintain the rest of the row (i.e. - company, Phone, etc.)  I have attached a sample of the data.

I am using Excel on a Mac.

Thanks

EJ

[#2 · Accepted solution]

@ECJ53 

I'm not a mac user, but with pivot it would be best. Here is an example of where you can make changes yourself.

I would be happy to know if I could help.

Nikolino

I know I don't know anything (Socrates)

* Kindly Mark and Vote this reply if it helps please, as it will be beneficial to more Community members reading here.

[#3]

@ECJ53 

Good data granularity rules would say to separate the lines to get the most of your data. If you do want you’re looking to do then you can’t filter on a state or zip code and really get value or show in a Pivot Table in the most effective way. 

There’s really now way to do what you’re doing. You’d effectively put it on another row meaning it’s no longer useful. It’s better to have to have them in their own column in that row:

Street Address City State Zip Code in their own columns in that same row. 

if you really need it like that you can type the street address then press ALT+Enter to return and then maybe a text wrap or resize the rows to show correctly:
123 Fake Street (press ALT+Enter)

Anytown, VA 20000

[#4]

Jpalaci1

I know this, but this data was provided by someone else and unfortunately that is how the prepared it. So I need to use the data in a CRM so that is why I need to convert the "two lines" to one.

Thank you for your input.

EJ

[#5]

@ECJ53  I'm not sure how that pivot table solves what you wanted but in the attached I added a column (address 2) that just points to the row 1 down in the address column.  Then with some slight format fixes of the pivot table I think you will now get what you want.  OR I inserted another sheet and using 2 formulas re-created that table without the mostly blank lines (1 formula to recreate the header line and 1 formula with a Filter() function).

Hope this helps.