Extract part of address from address field
Can I extract data from an address field so that I can format the address when printing it on a letter? Example Address field: 3300 North Brook Place, Boise, ID 83710 United States ...
Forum Discussion
If you look at the address field properties, you will see that it is really made up of 6 fields, and each has its own field name and Field ID#, so "yes" is the answer to your question.
Post back if you need help with the actual formula.
Post back if you need help with the actual formula.
- How do I extract the Zip Code subfield from the address field. I want to use this value in another formula. Basically, I want to make another field the value of the Zip Code subfield.
------------------------------
Jason Beaver
------------------------------- MarkShnier__You
Qrew Legend
The field name for that subfield is listed on the field properties of the [Address field]. One of mine looks like this
31 Address: Street 1 32 Address: Street 2 33 Address: City 34 Address: State/Region 35 Address: Postal Code 36 Address: Country
So my formula would just be
[Address: Postal Code]
------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------- thank you
------------------------------
Jason Beaver
------------------------------
- Hello, thanks for this article. I am a new user to Quickbase. I would like to leverage what you have mentioned above about how the address is made up of individual subfields.
I will be using the map report functionality to display mapping indicators for each of my client's data submission. In the map view, I do not want the numerical portion of the address to be displayed when hovering over the map indicator. How do I create a formula text field using the subfield? Thank you.
I found a formula for Excel but am having trouble applying it to quickbase. I inserted the field I saw mentioned in part of the discussion about using Address: Street 1.
LEFT([Address: Street 1],1),"",LEFT([Address: Street 1],FIND(" ",[Address: Street 1])-1)
------------------------------
Shuma Chaudhuri
------------------------------- MarkShnier__YouI can certainly help you with a formula, but I don't think there is a way with either the native Maps or the 3rd Party Add On from Juiced Technologies to not show the full address.
But let us know if you need help with an address based formula with an example of the format if the result. Maybe that would still be useful for you on a regular vanilla table report.
------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------
- Thanks so much for such a quick response, Mark. In my mind of working with my onboarding lead, we were thinking we can set up a formula-text field to store this "truncated" address of removing the house number, and then use this modified field to display on the map, where the actual address would still ensure the "marker" on the map is accurate.
Will that work? If not, then I will abandon the idea. I need the map feature badly for my organization, but I am very worried about displaying the PII visibly for basically anyone on the internet who will be able to see the map.
P.S. if it helps for me to explain my use case more, I am glad to do so.
------------------------------
Shuma Chaudhuri
------------------------------- MarkShnier__YouI'm sorry but in my testing I was unable to suppress the exact address from showing on a Maps report type. They allow you to show some fields in addition to the address , but I could not see a way to suppress the full address from showing.
------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
mark.shnier@gmail.com
------------------------------The way we were trying to approach this is to add a new field called Protected Address as a formula text field. Then for the formula, we were trying to use the Part function. For Kirk, the formula is working, where the house # is not included in this new field. But I can't get the formula to work.
Example Address
111 Main Street, Boston MA 12345
Here is the formula that Kirk had:
Part([Address: Street 1], 2, " ") & " " & Part([Address: Street 1], 3, " ") & " " & Part([Address: Street 1], 4, " ") & " " & Part([Address: Street 1], 5, " ")
His result was:
Main Street, Boston, MA 12345
But for me, my result comes back:Main Street
The hope was that once we got this formula working, then I could configure the map report to display this new "Protected
Address" field rather than the "Address" field.
------------------------------
Shuma Chaudhuri
------------------------------
