Often when you are processing customer records or doing mail merge, it might be useful to get initials from a given name, like JFK for John F Kennedy.
You can do this using simple text formulas (left(), mid(), find()) combined with if(). Here is how:
Assuming cell B3 has the full name, then this is the formula you can use to get the Initials:
=if(len(B3)-len(SUBSTITUTE(B3," ",""))=0,left(B3,1),if(len(B3)-len(SUBSTITUTE(B3," ",""))=1,left(B3,1)&mid(B3,find(" ",B3)+1,1),left(B3,1)&mid(B3,find(" ",B3)+1,1)&mid(B3,find(" ",B3,find(" ",B3)+1)+1,1)))
As you can see, I have used different logic to find initials, based on the number of spaces in the name.
For the sake of simplicity I have limited the formula for names with three, two and one part only (ie first name, middle name and last name), for some reason if the name has more than 3 parts, then this formula would result in initials for the first three chunks of the name. See the example on google docs.
More on names and text formulas: Find word count using excel formulas, 15 excel formulas for everyone, Generate tag clouds using VBA.
3 Responses to “Filter one table if the value is in another table (Formula Trick)”
What about the opposite? I want a list of products without sales or customers with no orders. So I would exclude the ones that are on the other table.
Good question. You can check for the =0 as countifs result. for example,
=FILTER(orders, COUNTIFS(products, orders[Product])=0)
should work in this case.
PS: I have added this example to the article now.
Hi there!
Could i check if there was a way to return certain fields of the table only?
so based off your example above, i would like to continue to use the 'Products" table as a way to filter out items from my "Orders" table, but only want to show maybe only the "Product" and "Order Value" fields, rather than all 5 fields (sales person, customer, product, date, order value).