Feb 23 2021 09:23 AM
Feb 23 2021 09:23 AM
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.
Feb 23 2021 10:16 AMSolution
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.
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.
Feb 23 2021 10:26 AM
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
Feb 25 2021 02:58 PM
Feb 25 2021 03:57 PM
@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.