Forum Discussion
VLookup with Comma Interval
Hi all,
I would like to use VLookup to abstract the phone number and email based on the name given in cell B3 and subsequent column. However, in between the name, there is a comma, may I know is there any method to achieve this?
9 Replies
renjie0315
Perhaps with M365...
=SORT(UNIQUE(WRAPROWS(TOCOL(WRAPCOLS(TOCOL(TEXTSPLIT(TEXTJOIN("♡",1,B3:D4),{"♡",", "})),2),,FALSE),3)))Hi Riny_van_Eekelen,
I have downloaded your excel file. However, I can't find any formula or method that you have done, it's a plain file basically. Would you mind to show me where can I locate it?renjie0315 I've used Power Query (PQ). If you are not familiar with PQ, it's not so easy to just start using it. You could start learning from the site in the link below.
It clearly explains all the basics and more advanced features of Power Query.
https://exceloffthegrid.com/power-query-introduction/
=INDEX(TEXTSPLIT(TBL[Name],","),1,1). You might need =TRIM too.
- Hi ecovonrein,
May I know what is TBL[Name]? It shows there's a problem with this formula. May I know how should I solve this?Hi ecovonrein,
I presume TBL stands for Table. This is the error that I faced when I select the table.
