I've been wanting to start this thread for awhile, so here goes.
I know many of us use Excel to manage portfolios, big and small. Mine has developed over time as I learned new functions to implement. I thought it would be handy to have a central repository of those tips that anyone could use and not have to reinvent the wheel. Hopefully, there are some Excel masters that can share what they do. I do assume at least a basic level of Excel familiarity here.
I'll start with a way to automatically separate out the TLD into its own column.
So like probably everyone, my first column is the full name of my domain, including TLD. I realize I could do one column for name and another for TLD manually, but I like to have my spreadsheet do as much automatically as possible. The reason for having these two columns is that sometimes I want to look at full names, and sometimes I want to analyze my portfolio by TLD composition.
To do this I use the "Right" command written like this: =RIGHT(A19,LEN(A19)-FIND(".",A19)) This formula would be in cell B19 and the full name would be in A19.
The Excel RIGHT function extracts a substring from a string starting from the right-most character.
The Excel LEN function returns the length of the specified string. So I am using the DOT as the starting point
The Excel FIND function returns the location of a substring in a string. The search is case-sensitive.
In common language, this string starts on the right of the entry and moves inward until it finds a dot. Then it captures everything in between the end and the dot and uses that data to fill the cell.
So now, when I enter the full name in the first field, the second field is automatically populated with just the TLD.
That's my tip, hope it helps someone....I hope others have more to share.
I know many of us use Excel to manage portfolios, big and small. Mine has developed over time as I learned new functions to implement. I thought it would be handy to have a central repository of those tips that anyone could use and not have to reinvent the wheel. Hopefully, there are some Excel masters that can share what they do. I do assume at least a basic level of Excel familiarity here.
I'll start with a way to automatically separate out the TLD into its own column.
So like probably everyone, my first column is the full name of my domain, including TLD. I realize I could do one column for name and another for TLD manually, but I like to have my spreadsheet do as much automatically as possible. The reason for having these two columns is that sometimes I want to look at full names, and sometimes I want to analyze my portfolio by TLD composition.
To do this I use the "Right" command written like this: =RIGHT(A19,LEN(A19)-FIND(".",A19)) This formula would be in cell B19 and the full name would be in A19.
The Excel RIGHT function extracts a substring from a string starting from the right-most character.
The Excel LEN function returns the length of the specified string. So I am using the DOT as the starting point
The Excel FIND function returns the location of a substring in a string. The search is case-sensitive.
In common language, this string starts on the right of the entry and moves inward until it finds a dot. Then it captures everything in between the end and the dot and uses that data to fill the cell.
So now, when I enter the full name in the first field, the second field is automatically populated with just the TLD.
That's my tip, hope it helps someone....I hope others have more to share.
