Substitute Formula for random strings of phone numbers
We have a column for phone #s that is filled by our staff via a webform. As you can imagine, this leads to a lot of randomly formatted numbers.
Ex.
(555)555-5555
555-555-5555
555.555.5555
5555555555
I would like to construct a formula that will remove all the dashes, dots, spaces, parentheses, or even stray letters, to leave me with a nice clean string of numbers that I can write another formula to format into...
(555) 555-5555
everytime.
Any assistance would be appreciated, as I thought I would be able to nest the SUBSTITUTE formula, but I can't seem to make that work. Thanks!
Comments
-
By the way, the reason this matters has to do with our field agents who view the numbers on their mobile devices. The software on these devices will not recognize a string of numbers as a phone number without it being properly formatted.
When the number is properly formatted, the field agent can click it within the Smartsheet app which will launch the phone app and make a call automatically. If the number is not formatted properly, they have to manually enter it after opening the phone app. Annoying. -
You can nest SUBSTITUTE functions. It looks a little awkward, but it can be done. The easiest way I have found to do this is to work from the inside out. Start with your first one...
SUBSTITUTE([Phone Number]@row, ")", "")
This will pull all of the closing parenthesis out. We are now going to "nest" this formula in the [text to search] part of another SUBSTITUTE
SUBSTITUTE(first formula, "(", "")
So now we are taking the closed parenthesis out.
Nest it again inside of another SUBSTITUTE function in the [text to search] part...
SUBSTITUTE(second formula, "-", "")
SUBSTITUTE(third formula, " ", "")
SUBSTITUTE(fourth formula, ".", "")
What you end up with is...
SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE([Phone Number]@row, ")", ""), "(", ""), "-", ""), " ", ""), ".", "")
The above will remove:
(
)
-
spaces
.
To remove random letters... That gets ugly. You will need an additional SUBSTITUTE for each letter...
.
Another option would be to use a series of MID statements to parse the data out across a series of columns. You would then be able to use a JOIN(COLLECT formula with an ISNUMBER function to pull only the cells that have numbers in them thus leaving out all letters, spaces, and punctuation.
I honestly feel that this may end up being the better solution than nested SUBSTITUTE functions.
-
Side note: Even if you add this formula, I would still add a subnote on the form to recommend the data input format.
Ex:
Phone Number
Format (555)555-5555
Also I recommend submitting an enhancement request for data validation in webforms as this would be useful for many different scenarios.
-
I was thinking about a solution to this that was a little more straightforward and came up with the idea of checking each character to see if it is a number
=IFERROR(INT(LEFT([Column3]7, 1)), "") + IFERROR(VALUE(MID([Column3]7, 2, 1)), "") + IFERROR(VALUE(MID([Column3]7, 3, 1)), "") + IFERROR(VALUE(MID([Column3]7, 4, 1)), "") + IFERROR(VALUE(MID([Column3]7, 5, 1)), "") + IFERROR(VALUE(MID([Column3]7, 6, 1)), "") + IFERROR(VALUE(MID([Column3]7, 7, 1)), "") + IFERROR(VALUE(MID([Column3]7, 8, 1)), "") + IFERROR(VALUE(MID([Column3]7, 9, 1)), "") + IFERROR(VALUE(MID([Column3]7, 10, 1)), "") + IFERROR(VALUE(MID([Column3]7, 11, 1)), "") + IFERROR(VALUE(MID([Column3]7, 12, 1)), "") + IFERROR(VALUE(MID([Column3]7, 13, 1)), "") + IFERROR(VALUE(MID([Column3]7, 14, 1)), "") + IFERROR(VALUE(MID([Column3]7, 15, 1)), "") + IFERROR(VALUE(MID([Column3]7, 16, 1)), "") + IFERROR(VALUE(MID([Column3]7, 17, 1)), "")
Apparently =value("-") does not pop an error and instead posts 0 which is weird to me. I guess it treats it like -0.
-
I second both of these suggestions ^^^^^
-
I thought about this, and it has the same drawback as my parsing solution...
Depending on how fat some fingers get, they could exceed the number of characters that have been accounted for.
Say we account for 20 characters. 18 would be looking at an ideal phone number of (###) - ### - ####. 19 for any errant letters or an extra space. But that only leaves a 1 character buffer for really fat fingers or someone accidentally typing the name next to the number or something to that affect.
That provides for a VERY long formula in this format or quite a few extra cells for parsing. Even your long formula above is still 1 statement short.
-
Wow! Thank you for the excellent--truly excellent--response! This is very helpful.
May I ask how you became so proficient at these formulas, and mastering the syntax and logic? Was this just trial and error, or is there some training guide or program that I am not aware of?
It seems you are seeing more deeply into the construction of these formulas than I can at the moment, so I'd be interested in learning how you got there. Thanks. -
I didn't ever have any formal training. I mostly just tested out my ideas and kept trying new things. Answering questions on the community is a great option. I am the head of smartsheet development for NAFTA for my company. I try to answer 2-3 questions on the community a day, especially those that require formulas, so that I never have a question that I don't know the answer to coming from one of my developers, and to learn different
I am not the best at smartsheet though, in fact Paul who answered first generally has more reliable answers, though I do try to beat him sometimes
If you want to learn your formulas, I highly recommend the smartsheet webinars. They are free, very professional, and very insightful. I learned things off of the basic user one that I didn't know after a year of using smartsheet.
-
L@123
Haha. Thanks for the shout out. I have learned a fair amount from your answers as well. Especially when it comes to the COLLECT function.
I have learned the same way. I had some very basic knowledge within excel from years ago, but I hate when something "can't" be done. My managers would come to me asking if something was possible in Smartsheet, and I would make it happen.
Trial and error and having a really hard time saying "can't" are what has gotten me to where I am today. I have watched a few of the basic videos offered, but most of my inspirations have actually come from the community.
I would hop on to ask a question and then see someone else's question that I could help with. That would lead me down a rabbit hole, and then suddenly I am getting some crazy ideas for workarounds.
My managers LOVE having as much as possible automated, and the level of automation they want just simply cannot be done without formulas. They threw out a challenge and I accepted. Over and over and over again. I learned out of pure necessity. Haha.
-
Colin,
My best suggestion is curiosity and thinking outside of the box.
Oh! And don't be afraid of helper columns. When I first started I was building out massive formulas that were VERY easy to break and VERY hard to fix, just so I could avoid having a bunch of extra columns.
Now... I have one sheet that literally has 200+ helper columns just to run automations on 6 working columns. It's A TON of automations that are happening all at once, but it is very easy to get in and diagnose/fix when anything breaks because it is so broken out and organized into various sections.
Just keep plugging away and experimenting, and you'll have the hang of things in no time.
-
I had some thoughts on this. When I went to test them, I found that this formula is adding all of the numbers together such that
555-555-5555
does not produce
5555555555
as desired but instead returns
50 (the sum of all of the numbers.
Adding a + "" somewhere would turn this into the string as desired, but would then no longer be recognized as number. I am not sure if that is an issue or not.
I still haven't found an efficient way to solve this without having to worry about a specific number of characters and/or types of characters. An easy and efficient "catch all"...
-
Good advice! I made the same mistake with long formulas and am learning more and more to rely on helper columns too. Thank you.
My biggest issue is understanding how SS reads and understands a formulas syntax, and being able to innovate without getting absurd errors.
Like nesting or stringing together multiple SUBSTITUTE commands. That formula above looks weird, and I just don't understand what the grammatical rules are for properly constructing that statement. That's my primary issue now. Just knowing those rules so I can be more creative.
-
The easiest way is to break it down. You could also take my long list of SUBSTITUTES and spread each one out over multiple helper columns. Then daisy chain those helper columns together with individual SUBTITUTE functions.
.
First Helper
=SUBSTITUTE([Original Text]@row, " ", "")
.
Second Helper:
=SUBSTITUTE([First Helper]@row, "-", "")
.
Third Helper:
=SUBSTITUTE([Second Helper]@row, ",", "")
.
If you can reference a cell that already has a formula in it, then you can also nest it. You don't have to actually SEE a string of text to be able to run a SUBSTITUTE function on it. That string just has to be there somewhere.
If I wanted to nest the above example, I would start back over at the beginning.
The First Helper references original data. No parts of the formula can be replaced.
The Second Helper references the First Helper, which means that you can replace "[First Helper]@row" with the actual formula that is in the First Helper column.
Then we move on to the Third Helper which references the Second Helper that now includes the formula from the First Helper column. Move the (now nested) formula from the Second Helper into the [Second Helper]@row section.
You have now successfully nested SUBSTITUTE functions.
.
I use this method quite frequently. I will break every single part of a solution out into numerous helper columns down the the absolute most basic functions possible to first make sure that every piece is working as it should.
Then I will go through and combine some of the more simple functions in the manner above because lets face it... I don't need a helper column to add two cells together and then another helper column to add in a third cell. So I will combine those two helpers in the manner outlined above.
I leave the more complex parts as stand alone functions in their own helper columns, and I make sure to arrange the helper columns in chronological order from left to right. Organizing them this way helps me more quickly narrow down where an issue may be when the end result is not correct or throwing an error.
.
As for the creativity part... This is a rather long winded response. If you want the "long story short" version, go ahead and jump to the bottom.
Break the functions down into their own sections. For example:
You will see quite frequently here in the community that an INDEX/MATCH is more highly recommended than a VLOOKUP. Well an INDEX/MATCH can be a little intimidating and confusing at first if you have never dealt with anything too terribly complex.
But we can break this down and focus on each section individually (kinda like we did with nesting).
.
=INDEX(data_you_want_to_display,
This is the easy part. Select the range where the data to be displayed is coming from.
.
row_number,
This is where you need an actual number to specify a row number. This is when you look through the list of all functions that produce a number to find the best one that suits your needs. You can could manually input a number, use a COUNT(IFS) or SUM(IFS) function, any of your mathematical functions (division, multiplication, subtraction, etc.)... This particular example, we are trying to pull data from [Column A] based on criteria from [Column B] matching up. The MATCH function displays a number that represents where within a range the cell containing that particular match is found. So let's look at the MATCH function.
.
=MATCH(search_value,
Pretty simple. What value are we looking for?
.
range,
Where are we looking?
.
[search_type]
Optional, but I recommend 0 which provides for an exact match.
.
So now we just built out our MATCH function to be used as the row number of our INDEX function.
Let's return to the INDEX function. Now we have the first two parts of our INDEX function.
=INDEX(data_you_want_to_display, row_number,
.
[column_number]
This is optional and only needed if the range in your first part covers multiple columns. This section requires a number, so we can treat it exactly how we treated the row number portion. If you want to specify a column, you need a number. How you get that number is up to you.
.
There are a lot of resources that can be taken advantage of when understanding which formula best suits your needs.
I very strongly recommend finding the "Formulas Sample Sheet Template". It is an interactive sheet that has examples of all of the different functions and lays everything out.
Obviously the community is a great place to find help.
This link is an explanation of all of the types of errors you could get and what they mean:
And following along in the helper box that pops up when you are writing out a formula also really helps keep you on track.
.
Very long story short...
When it comes to creativity, think in as small sections as you can and forget about the big picture for a minute. If you need a number, think of all the different ways you can display a number. Don't worry about "normal" or "usually". Worry about what works for that one very specific part.
-
Ok I have read this discussion because I am trying to do the same thing, but have only been using smartsheet for a very SHORT time and the community has helped me with a ton of formulas but my question to you is what is a helper column and how does it work and what what is a daisy column.
-
A helper column is something your users should never touch/interact with, but instead performs a step in a calculation. This can be used to simplify equations, and even perform complex actions for situations smartsheet's formulas could normally not handle.
a daisy chain is a euphemism for things linked together, not a type of column.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 437 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
