Forum Discussion

AndyT410's avatar
AndyT410
Brass Contributor
Apr 05, 2023
Solved

Return all cells that start with *

Hi, I'm trying to return all cells that start with * followed by a space or just *. With * being a wildcard I can't figure out how to do it. The * is part of the name from an export and I need to id...
excel
Formulas and Functions
Hecatonchire's avatar
Hecatonchire
Iron Contributor
Apr 05, 2023

Hi

 

=filter(range;left(range)="*"))

 

AndyT410 

AndyT410's avatar
AndyT410
Brass Contributor
Apr 05, 2023
Hi,
Sorry. Me again. I thought it would also work for cells starting ** but I can't seem to make it work. Is this possible as it returns both * and **. Thanks in advance.
  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Apr 05, 2023

    AndyT410 

    Practically the same

    =FILTER(range, ( LEFT(range)="*") + (LEFT(range)="**")  )
    • mtarler's avatar
      mtarler
      Silver Contributor
      Apr 05, 2023

      Similar answer but I think I interpreted different than Sergei:

      For "*" and NOT "**"
=FILTER(range, ( LEFT(range)="*") * (LEFT(range,2)<>"**")  )
and for "**" and not "*"
=FILTER(range, LEFT(range,2)="**"  )
      • AndyT410's avatar
        AndyT410
        Brass Contributor
        Apr 06, 2023
        Thanks. That works perfectly.

Resources