Hello all,
I have an excel sheet with about 50K rows that have hostnames with FQDNs. I'm looking for a formula to strip down FQDN's with exception of IP addresses.
Real examples:
Column A
10.10.10.10
hostname1-P.test.com
hostname2.test.edu
hostname1.rc.test.org
In column B I want:
10.10.10.10
hostname1-P
hostname2
hostname1.rc
How I can accomplish this?
Thanks in advance
what is a FQDNs?
Please read the yellow banner at the top and upload a sample WB
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
One way:
See formula in FILE, as forum software did NOT render it correctly here.
Glenn
None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.
Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh
It means "Fully Qualified Domain Name"
Last edited by AliGW; 09-24-2022 at 09:21 AM. Reason: Please DON'T quote unnecessarily!
This works like magic Glenn! I appreciate it so much.Originally Posted by Glenn Kennedy
Also, curious, how did you become this good at Excel? I want to learn!
I learned most of it right here!
You're welcome.
It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.
Finally, if that takes care of your original question, please click on "Thread Tools" from the menu link (just above the first post in the thread) and mark this thread as SOLVED.
Hi Glenn.
How can I return the actual values of the servers instead of #VALUE where there is no domain name to strip?
For example, some hostnames don't have anything to strip but they return #VALUE in the results. How can I display their names regardless?
A guess, as no specific example was provided.
See file.
Hello Glen. Thanks again. This works as well. Turns out that I need to remove hostname1.rc.test.org to hostname1 as well. So I am assuming this formula would work?
=IFERROR(IF(ISERROR(LEFT(A2,3)+0),LEFT(A2,FIND(".",A2)-1),A2),A2)
Away for a few hours. Please upload a fresh sample that covers all scenarios and includes expected answers.
Hi Glenn. Thanks for helping. Attached is expected results marked in yellow exmaple of results.xlsx
Then use:
=IF(A2="","",IFERROR(IF(ISNUMBER(SUBSTITUTE(A2,".","")+0),A2,LEFT(A2,SEARCH(".",A2)-1)),D2))
Thank you Glen. I appreciate it. I'll use the formulas provided when necessary.
You're welcome.
-------------
Sorry Glenn but I think I am missing something here.
With this formula you provided: =IF($A2="","",IF(ISNUMBER(SUBSTITUTE($A2,".","")+0),$A2,LEFT($A2,SEARCH("?",SUBSTITUTE($A2,".","?",LEN($A2)-LEN(SUBSTITUTE($A2,".",""))-1))-1)))
I got these results:
Column A (original data)
10.10.10.10
hostname1.test.org
hostname1.rc.test.org
hostname1
Column B (results of formula)
10.10.10.10
hostname1
hostname1
#VALUE!
I would like this if possible:
hostname1
hostname1.rc
hostname1
I don't think any of the formulas return said results? Let me know.
Did you open the file at my last post????
It delivers the correct result for BOTH scenarios. Unless there's something you haven't told me about.
Yes, we are all good. I didn't realize that the other column had the results as well.
Now I am finally good and thank you again for all the help. I really appreciate it.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks