In the last post in Excel Category I talked about text functions and I posted a situation to create a distribution list from list of email ids in a column. There is no formula in excel which you can use to create a distribution list. But good news is that you can create one using VBA. Below is the code which would give a formula in excel to use for distribution list.
There are many ways to get this function written but below is one of the easiest way to understand the code. Paste the below code in any vba module and use it as a normal formula in excel.
Function listcat(src_range As Range, diff As String) As String
‘src_range is range variable to allow range selection and diff is variable to store ‘differentiators
Dim final As String
‘variable called final to store the distribution list
Dim c As Range
For Each c In src_range
final = final & diff & c.Value
‘finally taking the clean distribution list to listcat
listcat = Right(final, Len(final) – 1)
That is it go back to excel and use this like a formula in excel, for ex: =listcat(B2:B9,”;”) where B2:B9 is the list of email ids which needs to be added and “;” is the separator.
Attached is the file for your reference with the code and formula hope you will find it useful.
Note: These days you can directly copy the entire values in the column and paste in outlook/other mail clients which will automatically create a list. But if in situations it doesn’t this is the best way.
In the last post on text functions we saw trim and clean. For those whow work with lots of data analysis and specially if the data is coming from various sources he gotta use more than just trim to transform the data into meaningful information. One of the most useful function that I use is “Left”. Suppose you have “UHG212″ in a cell A1 and you need to extract the first 3 characters from the cell in A2 then you use this formula in A2:
This will give you the first 3 characters from left and you can drag this formula to apply it for the entire column. To extract text from right you use the similar one =right(a1,3). Well that was easy but consider this situation you got a list of values like the below in cells B1 and B2 respectively.
In the above 2 values the text part is a course code and the numeric is its id number so I want to extract only the text part. (Of course you can use text to columns.) so to get this with a formula I need to know what is the position of underscore in each entry. So I know of a formula which would give me that which is =find(“_”,b1,1) which would return 5 for EXTT_23. So now to extract the text part out of it, you mix the left formula and the find which would be like this:
This would give me the answer EXTT and how does that work? The find formula gives me the result 5 and I am substracting one from it to make it 4. Now using left function to get the 4 characters from left.
Just drag the formula to the remaining cells and it would give you the required course codes for each entry.
The data for the same with formulas is in this file (techflames_practice.xlsm), you can download and check it for your reference.
Before I wrap up I would also like to tell little about concatenate with an example. Suppose you’ve got info,@,techflaemes.com in 3 cells a1,b1,c1 respectively and you gotta join them to make the email id email@example.com how would you do it? 2 Ways one is using the formula in say cell d1 which is =concatenate(a1,b1,c1) which will give you the result else the direct way would be this formula =a1&b1&c1 which also gives you this result. Ok now I leave with this thought what if you have 100 emails in a1 to a100 cells and you want to make a distribution list i.e. join them using a coma in between. Are you gonna write a lengthy formula like this =a1&a2&a3&a4…till &a100. Well for such things you got vba which can help you do this quickly. Will post the code in VBA section very soon.
Cya until next post.