You can make a difference in the Apple Support Community!

When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.

Looks like no one’s replied in a while. To start the conversation again, simply ask a new question.

Using Categories to Populate Locations

I have nearly 400 clients. I want to populate each address into a spreadsheet within that workbook.


Example. 30909 populates into a cell next to a cell labeled Augusta.


Do I have to create that spreadsheet and cell, and then manually add those locations, or is there a way to get it done automatically using Numbers?


Thanks in advance,

RC

MacBook Pro 15″, macOS 12.4

Posted on Jun 25, 2022 4:43 AM

Reply
Question marked as Top-ranking reply

Posted on Jun 25, 2022 9:37 AM

You don't have to create a column for the zip code


I imported the zip code data that was recommended by SG in a sheet.

Column A is now formatted as text.


With XLOOKUP you can search for the zip code and get the County and Town / City as a result.

Formula for cell C2: = XLOOKUP(REGEX.EXTRACT($B2,"\d+$",match-occurrence,capture-group-occurrence),zip_code_database::Table 1::A,zip_code_database::Table 1::H,"--",0,1)

Formula for cell D2: = XLOOKUP(REGEX.EXTRACT($B2,"\d+$",match-occurrence,capture-group-occurrence),zip_code_database::Table 1::A,zip_code_database::Table 1::D,"--",0,1)




Here the 2 sheets that I use


Ralf

Similar questions

9 replies
Question marked as Top-ranking reply

Jun 25, 2022 9:37 AM in response to rcandrewz

You don't have to create a column for the zip code


I imported the zip code data that was recommended by SG in a sheet.

Column A is now formatted as text.


With XLOOKUP you can search for the zip code and get the County and Town / City as a result.

Formula for cell C2: = XLOOKUP(REGEX.EXTRACT($B2,"\d+$",match-occurrence,capture-group-occurrence),zip_code_database::Table 1::A,zip_code_database::Table 1::H,"--",0,1)

Formula for cell D2: = XLOOKUP(REGEX.EXTRACT($B2,"\d+$",match-occurrence,capture-group-occurrence),zip_code_database::Table 1::A,zip_code_database::Table 1::D,"--",0,1)




Here the 2 sheets that I use


Ralf

Jun 25, 2022 8:20 AM in response to rcandrewz

rcandrewz wrote:

at the very least I would love for it to populate in groups the zip codes.


From the example you posted it looks as if you can easily extract the postal code into a separate column, with something like this:



=REGEX.EXTRACT(B2,"\d+$")


(replace the , with ; if your machine is set to a region that uses , as a decimal separator)


Once you have the zip codes in a separate column then it is easy to group by postal code by adding a Category.





However, looking up the city/town/subdivision in an automated fashion is not something Numbers can do natively. That will require using one of the various api's available. or perhaps looking up the data in a spreadsheet that can be downloaded for a fee.


BTW, Numbers does not have "workbooks." The equivalent of an Excel workbook is called a document or spreadsheet. And a sheet in Numbers is a blank canvas on which you can place multiple tables, charts, shapes and other objects. A table contains a grid with rows and columns of cells. Therefore, I labeled it "Master Table" and not "Master Sheet."


SG

Jun 25, 2022 5:51 AM in response to rcandrewz

Could you please post a screenshot of your address list, maybe a mix of 10-15 in different cities.

Show these addresses in a second screenshot in the format that you’d like to have as the end result.


That will make it easier to get exactly what you want.


Please replace all company names with dummies, then nobody can see confidential information.


Ralf

Jun 25, 2022 7:36 AM in response to Ralf-F

I generated them by hand and went about the laborious and time consuming research. That is why I was wondering how to go about setting up a workbook and spreadsheet(s) that would do it automatically when the zip code gets populated.


I am not sure if I am explaining this correctly.


BUT at the very least I would love for it to populate in groups the zip codes.

Jun 25, 2022 8:38 AM in response to SGIII

I understand, but because it took Apple 30 years to create a spreadsheet application better than Excel as an Apple user since the late 80s, some old habits die hard, such as calling a document a workbook.


But That was very helpful and much appreciated. I should also note that I am using an Excel workbook file sent to me and converting it into my "environment."


I will then send everything as a PDF back to them.


It was tough because whoever entered the data had 15 addresses in one field on one spreadsheet and what you see on most other spreadsheets.


The whole time I thought MAYBE Numbers would have a work-around that didn't require me having to make a zip code column, nonetheless.


Thank you.


RC

Using Categories to Populate Locations

Welcome to Apple Support Community
A forum where Apple customers help each other with their products. Get started with your Apple Account.