New Data Types - Extracted data can't be assigned a data type?

Hi ZWall,

Thank you for querying in this forum. We do understand the inconvenience caused and apologize for it. And we will try our best to help you.

According to your description, it seems that you are using new data type in Excel, generally, if you typed zip codes and assigned zip codes to the Geography data type, it will be changed to "Minneapolis, Minnesota". And you want to type "Minneapolis, Minnesota" and assign it the Geography data type, it works fine when you get the 44.9833, -93.2667, however, if you tried zip code, it will appear an error message.

May I know if my understanding is right? If so, to better understand your situation and do more tests on our side, could you provide the information below with us?

1. Could you provide the product Information and version of your Excel? You can go to Excel > File > Account and provide the screenshot of it. As shown below:
2. Could you provide the screenshot of error message?
3. May I know if the other locations have the same problem?

Best Regards,

Sukie

Hi Sukie,

I think the new data types are working as intended—I'm pretty sure that what I'm encountering is not a bug but instead a deliberate limit placed on these new data types. As the error message states, formulas can't be converted into the new data types.

Here's a small test of this:

In Cases 2 and 4, I cannot convert the cells in row 2 to the Geography data type because they're formulas (=B2 and =E2.City). If I try, I get the following error message:

That's a long dialog box! :)

To rephrase my original question: Every zip code has a City. Every City has Latitude and Longitude values. How do I get Latitude and Longitude values from a zip code? (Such that if I change the zip code, the Latitude and Longitude values will automatically update.)

Thanks for your time!

Zach

Hi Zwall,

We appreciate that you posted back and provided more details with us. I’m sorry for the late reply.

We have tested it on our side according to the steps you provided and we can reproduce the same situation as yours. And you are right, generally, if we assign Geography data type to zip code and it will be changed to city, and this cell will contains formula, we may not assign a formula Geography data type, that’s may why you encountered this alert. We also appreciate that you spent your precious time sharing your test results and experience with us.

For your question about get Latitude and Longitude values from a zip code, we noticed that you mentioned that you don’t want to use macros, if so, we’re afraid that there is no out of box way to achieve your requirement directly. We have also tested a lot and searched a lot, we may not find a way to convert Zip code to Latitude and Longitude values directly. We’re sorry that it doesn’t meet your requirement. We do understand the inconvenience caused and apologize for it.

We understand the situation and you make a great point. To address your concern about the situation, we’d suggest you go to Excel UserVoice to provide your feedback. This is the best platform to let us hear from you and make our products and services better for you and others. Or you can also provide feedback to our related team by clicking on File > feedback insider Excel. This feedback will directly go to them so that our related team can hear your voice on this feature directly.

At the same time, we will keep this thread open, welcome the other community members to share your suggestions here.

Your understanding and patience will be highly appreciated.

Best Regards,

Sukie

Hi Zach, 

Thank you for your feedback!

If I understand correctly, it looks like you are trying to extract to grid latitude and longitude for 55401. Assuming that is correct, you're asking why you can't just get Latitude and Longitude from "55401"?

The reason why you can't get latitude and longitude from "55401" is because the data provider doesn't provide those two fields for ZIP Codes, but it does for cities - that's why you see those fields for "Minnesota". This is a provider limitation rather than a feature limitation.

Extracting latitude and longitude from Minneapolis is a good workaround, otherwise if you are a Microsoft365 Personal or Family subscriber (Buy Microsoft 365 Family & Personal Subscriptions (Formerly Office 365) - Microsoft Store), you can use the new City data types we have released to the Beta channel of the Office Insiders program (When do I get the newest features for Microsoft 365? - Office Support) - the data is coming from Wolfram Alpha and they have provided latitude and longitude for ZIP Codes. 

Thank you!

Elisabetta, Excel team

Hi Zach, 

Thank you for your feedback!

If I understand correctly, it looks like you are trying to extract to grid latitude and longitude for 55401. Assuming that is correct, you're asking why you can't just get Latitude and Longitude from "55401"?

The reason why you can't get latitude and longitude from "55401" is because the data provider doesn't provide those two fields for ZIP Codes, but it does for cities - that's why you see those fields for "Minnesota". This is a provider limitation rather than a feature limitation.

Extracting latitude and longitude from Minneapolis is a good workaround, otherwise if you are a Microsoft365 Personal or Family subscriber (Buy Microsoft 365 Family & Personal Subscriptions (Formerly Office 365) - Microsoft Store), you can use the new City data types we have released to the Beta channel of the Office Insiders program (When do I get the newest features for Microsoft 365? - Office Support) - the data is coming from Wolfram Alpha and they have provided latitude and longitude for ZIP Codes. 

Thank you!

Elisabetta, Excel team

Hi Elisabetta,

I appreciate you taking the time to look into this, and am interested in checking out the additional data types as they appear.

However, while I was using the Zip Code -> Lat / Long scenario as an example, my actual question is more straightforward:

Why can't we assign one of the new data types to a formula?

The scenario about zip codes, cities, and coordinates in my original post was simply meant to illustrate a case where this feature would have been useful, and where this feature limitation prevented me from solving my problem.

I understand that the provider for the zip code data doesn't include coordinate data; that's why I was trying to bridge the gap by extracting the city from the zip code, then extracting the coordinates from the city. However, Excel doesn't allow me to assign one of the new data types to the city extracted from the zip code, because technically it's a formula.

I realize that "flattening" the data from a formula into a string, either manually using "paste as values" or programmatically with a macro, would provide one way forward. Unfortunately, that whole process is less convenient than our current system, which involves bloated data tables and a whole lot of XLOOKUP.

Apologies for going on and on—I am excited about the new data types and would love to advocate for our company to start using them, but I really can't recommend it unless we are able to assign the new data types to *formulas as well as static values.

Cheers,

Zach

Hi Zach,

now I get what you're saying, sorry about the confusion! 

Yes you're right, currently you can't turn a formula into a data type because you would lose the formula and just keep the value of it, and the workaround you mentioned (paste as value) is exactly the best way to do it as of now. 

Thanks so much for using data types and sharing your feedback with us, it's very helpful to know that you'd like to turn formulas into data types. We will look into this!

Thanks,

Elisabetta, Excel team