You can make a difference in the Apple Support Community!
When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.
When you sign up with your Apple Account, you can provide valuable feedback to other community members by upvoting helpful replies and User Tips.
I have a 16,000 line Numbers spreadsheet with a column that contains Email addresses. I want to search each address for ".jp". If it is found, I want it to write "Japan" in the adjacent cell, where the formula resides. Is there a simple way to do this? Numbers does not seem to have a CONTAINS function to search for strings. That would be very useful.
iMac 27″, macOS 10.15
airdsd wrote:
Is there a simple way to do this? Numbers does not seem to have a CONTAINS function to search for strings. That would be very useful.
COUNTMATCHES provides a simple way to search if a string "CONTAINS" a substring in any position.
=IF(COUNTMATCHES(A2,".jp")>0,"Japan","")
SG
airdsd wrote:
Is there a simple way to do this? Numbers does not seem to have a CONTAINS function to search for strings. That would be very useful.
COUNTMATCHES provides a simple way to search if a string "CONTAINS" a substring in any position.
=IF(COUNTMATCHES(A2,".jp")>0,"Japan","")
SG
This may work for you:
assumes emails are in column B.
select cell C2, the type, or copy and paste from here, the formula:
=IF(LEN(IFERROR(REGEX.EXTRACT(B2,".+\.jp.*",0,1),"")) >0, "JAPAN", "")
shorthand for this is:
C2=IF(LEN(IFERROR(REGEX.EXTRACT(B2,".+\.jp.*",0,1),"")) >0, "JAPAN", "")
to be clear... I mean
select cell C2
then type:
=IF(LEN(IFERROR(REGEX.EXTRACT(B2,".+\.jp.*",0,1),"")) >0, "JAPAN", "")
then type return
to fill down, select cell C2, copy
select C2 to the end of the column, paste
Do you want it to return "Japan" if ".jp" is found anywhere in the string or just at the end? Which of the following should say "Japan"?
bob@email.jp
bob.jp@email.com
bob@email.jp.com
bob@jp.email.com (which does not have the dot before the "jp")
If only the first one where it ends in ".jp", a simple formula would be
=IF(RIGHT(B,3)=".jp", "Japan", "")
Dear SG,
Thank you! This is perfect, since my objective is to select the Japanese addresses, or alternatively, to eliminate the non-Japanese addresses. This can then be accomplished by sorting the spreadsheet on Column B and deleting the unwanted rows.
Excellent! Thanks again!
Steve
Dear Badunit,
Thank you! I attempted both of these functions before hearing from you, but REGEX was too complicated to follow and apparently I got the syntax wrong with the RIGHT function. Thank you for showing me how to use them!
Sincerely,
Steve
Is there a function that find a string in an Email address and write to an adjacent cell?
