address formula

Bob Crissmsm

What would be the formula to separate the address into plus for separation.  I have tried a few different ways and haven't had sucsess. 

Ichiorochi

Just to be clear, do you want the addresses to be put togther with the differnet value or do you want to split it apart?

Richard Rymill SBP

If you look at "Concatenation" in Smartsheet Help. http://help.smartsheet.com/customer/en/portal/articles/775363-using-formulas

Bob Crissmsm

looking to add the + 

replace spaces and conna's with plus symble

J. Craig Williams

Bob,

 

I assume the first result you have came from something like this:

 

=[AddressLine1]23 + "+" + City23 + "+" + State23 + "+" + PostCode23

 

and the second example is manual editing to show the result you desire.

 

Try this:

 

=SUBSTITUTE([AddressLine1]14 + " " + City14 + " " + State14 + " " + PostCode14, " ", "+")

 

Craig

Bob Crissmsm

Craig Thank you for your advice, you are correct in assumption.  and yes I came up with          =SUBSTITUTE([Street Address]114, " ", "+")

but I wasnt able to link the two steps together, to facilitate added couple columns and two stepped it.

 

=[fixed address]107 + "+" + City107 + "+" + State107 + "+" + Zip107. formula configure address with url infront to take to map or navigator from row in sheet or report.

 

www.google.com/maps/place/11712+W+Lark+Dr,+Chandler,+AZ+85287

 

I did try your example I am not getting something right.  However we are functioning with the extra steps.

J. Craig Williams

This works for me:

I do the substitute and addition of the URL in the same cell.

 

="www.google.com/maps/place/" + SUBSTITUTE([AddressLine1]14 + " " + City14 + " " + State14 + " " + PostCode14, " ", "+")

 

If it still doesn't work, you could share the sheet to me 

jcwill23@gmail.com

and I'll take a look.

 

Craig

J. Craig Williams

Update:

 

Bob shared the sheet with me.

I implemented the change above in a new column and suggested deletion of the previous four columns that were being used to pull the information together one piece at a time.

 

Craig