Excel formula that deciphers text and outputs properly-CodePudding

Is there a way to have excel read text and decipher whether it does or doesn’t have certain character/letters?

Here is my example sheet

I am looking for something that deciphers using these guidelines. 1. If entry has a / then output URL. 2. If entry is not a URL and has only numbers and special characters then output IP. 3. If entry is not a URL or IP and has more than 1 dots/periods/decimals then output HOST. If entry is not a URL, IP, or HOST (or only has 1 dot/period/decimal) then output FQDN.

Here is an example of what I'm looking for

I have tried using these below:


=IF(LEN(A1)-LEN(SUBSTITUTE(A1,"/“,””))=1,"URL",IF(LEN(A1)-LEN(SUBSTITUTE(A1,”.”,""))=1,"FQDN"‚IF(LEN(A1)-LEN(SUBSTITUTE(A1,".",”"))>1,"HOST")))

That works for reading URL, HOST, and FQDN; however, it reads IP's as HOST's.

I have also used


=IF(OR(ISNUMBER(SEARCH({"A","B","C",”D","E","F”,"G","H","I","J","K",”L”,"M",”N","O","P","Q”,"R","S","T","U","V","W",”X","Y","Z"},A1))),””,"IP")

That works for reading if an entry contains letters and if not it outputs IP.

Is there a way to combine these or simplify what I am trying to do?

Thanks!

CodePudding user response：

A possible solution (tested with O365) :

=IFS(ISNUMBER(VALUE(LEFT(A1:A5)))=TRUE,"IP",LEN(A1:A5)-LEN(SUBSTITUTE(A1:A5,".",""))>1,"HOST",LEN(A1:A5)-LEN(SUBSTITUTE(A1:A5,"/",""))=1,"URL",LEN(A1:A5)-LEN(SUBSTITUTE(A1:A5,".",""))=1,"FQDN")

Output :

CodePudding user response：

This produces the desired output for your sample (at least)

=IF(COUNTIF(A1,"*/*"),"URL",IF(ISNUMBER(VALUE(SUBSTITUTE(A1,".",""))),"IP",IF(LEN(A1)-LEN(SUBSTITUTE(A1,".",""))>1,"HOST","FQDN")))