I looked at the video by Kerry Rosvold on how to make a pipe filter in excel here and MVP Mark Brummel’s alternative here  . Both are awesome.


I have also an alternative way.


So this is how I do it.

You copy a few lines to excel like this:


And in Excel you paste, and Remove all other columns except No.




Add pipe (|) in B1 cell and =A1 in B2 cell and Enter



Add below formula in B3 cell and Enter
 =B2&B1&A3


Now it looks like



Re Select B3 Cell and select B1 value and Press F4 (which makes Pipe value Constant)



Now formula in B3 looks like
=B2&$B$1&A3

B1 is changed as $B$1


Copy B3 cell and paste in all below cells


The last row has the final filter.