I need to remove all area codes that are not 505 or 575
I am currently using an adapted formula from a different discussion
IF(AND(ISERROR(SEARCH("505",U12352,1)),ISERROR(SEARCH("575",U12352,1))),"BAD","GOOD")
However, a phone number like (123) 555-5056 is returned as good. Is there a way to limit the search to just the first 3 digits of the string?
Consider using a Filter, like this:
You can put the results in another table by first setting up the destination table, then selecting the visible cells in the filtered table, typing command-c, clicking once in a cell of the destination table followed by command-v or Edit > Paste and Match Style.
There are other possible approaches such as using REGEX to match the first characters, but I the Filter should be by far the easiest in this case. Less than a minute and you're done, without writing a single formula.
Filter data in Numbers on Mac - Apple Support
SG
Consider using a Filter, like this:
You can put the results in another table by first setting up the destination table, then selecting the visible cells in the filtered table, typing command-c, clicking once in a cell of the destination table followed by command-v or Edit > Paste and Match Style.
There are other possible approaches such as using REGEX to match the first characters, but I the Filter should be by far the easiest in this case. Less than a minute and you're done, without writing a single formula.
Filter data in Numbers on Mac - Apple Support
SG
Wait... I just added phone number formatting to the phone numbers and now I can search for "(505)"
Haha, thank you!
SOLUTION: IF(AND(ISERROR(SEARCH("(505)",U2,1)),ISERROR(SEARCH("(575)",U2,1)))," ",U2)
Then expand the columns on to the right and paste into 6 extra columns, select all new columns and copy snapshot, BOOM!
I can't believe I didn't think about formatting to area code in parentheses lol
Thank you @SGIII
JNRG_Admin wrote:
Can functions clear a cell?
You can use a formula to place a null character in a cell making it appear blank. So where you have "BAD" in a formula you could change that to "" and the result will appear blank.
You can place a filter on each column in turn.
You could also apply Conditional Formatting to highlight the bad ones (or good ones) in a specific color.
SG
That's kind of a pertinent "detail" omitted! It's generally not efficient to work with a horizontal list, but it should be possible.
Screenshot?
shift-command-4, drag to select area, release, start new post here and use the mountains-and-moon 'Image insertion' icon to insert the image from the Desktop.
SG
Sorry haha, just formatting the numbers and searching for the parentheses fixed it 100%
Unless you have an even easier solutions? files always have 10,000+ rows so automating would be very nice haha
Can functions clear a cell?
Here is the column layout i'm working with:
Better yet, is there an automator solution that does all the deletuing for me so im not sorting by bad or good and deleting the bad haha
I have up to 6 phone numbers in a row, can filter be used for multiple columns per row?
Apple Numbers Sort out Specific Area Codes From a List of Phone Numbers
