Finding and reporting special characters

[ashwithonline]

Hi Friends,

I need a formula where i can findout the special characters in a cell and the particluar special character is highlighted in the corresponding cell where the formula is.

Thanks in advance,
Jagdish

 

[AlanSidman]

suggest you provide a sample workbook with 8-12 records and then a mocked up solution of what you expect.

 

[ashwithonline]

Input: Jagdish@Shetty
Output: @

 

[AlanSidman]

Will it always be @ or will there be a list of special characters? If others, please supply list of all special characters to be extracted.

For this example, you can use Power Query and the following Mcode

Excel 2016 (Windows) 64 bit

	C​	D​
1​	Data	Custom
2​	Jagdish|Shetty	|
3​	Jagdish@Shetty	@
4​	Jagdish#Shetty	#

Sheet: Sheet1

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Text.Select([Data],{"@","#","|"}))
in
    #"Added Custom"