0

Filtering empty values from af list

Jet Reports Historic Posts

February 02, 2016 23:11

None

Follow

Hi,

I have a problem, where my Jet values return I my cell C5 like this: |||||||||||||||A|||||||||||||F|C|T||||||||||||||G|L|||||O|P|H|||||||||||||||||||||||||||

But I would like it to filter the empty values from the list at only show this:
A|F|C|T|G|L|O|P|H

Any suggestions?

• Facebook
• Twitter
• LinkedIn

4 comments

• 0
  
  Jet Reports Historic Posts February 03, 2016 08:35

  I use the filter "<>''" to get rid of empty values.

• 0
  
  Jet Reports Historic Posts February 03, 2016 23:09

  Well.. I can not filter it with <>
  I have tried.
  
  It seems that even though there is no value between |||||| it will show up. So I am looking for some way to separate the ||||| from the rest and get A|F|C|T|G|L|O|P|H

• 0
  
  Jet Reports Historic Posts February 04, 2016 08:10

  It makes me feel a little bit dirty but this seems to work for the example you give, assuming the text is in A1:
  
  =SUBSTITUTE(TRIM(SUBSTITUTE(A1,"|"," "))," ","|")
  
  It substitutes the "|" with spaces. The trim function removes spaces from the start and end of text, as well multiples between "words". By then substituting the "|" back in for the space you get the answer you want.

• 0
  
  Jet Reports Historic Posts February 04, 2016 21:36

  It makes me feel a little bit dirty but this seems to work for the example you give, assuming the text is in A1:
  
  =SUBSTITUTE(TRIM(SUBSTITUTE(A1,"|"," "))," ","|")
  
  It substitutes the "|" with spaces. The trim function removes spaces from the start and end of text, as well multiples between "words". By then substituting the "|" back in for the space you get the answer you want.

  
  I don't know if you got too dirty… :-)
  
  But it sure did the trick.. Thank you so much!! :-)