Forum Discussion
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 identify those particular names.
Column A
* Sample text 1
Normal text
* Sample text 2
etc.
Thanks in advance.
7 Replies
You can use a formula that combines the IF and LEFT functions to return all cells in a column that start with an asterisk (*) followed by a space or just an asterisk.
Here’s an example formula that you can adapt to your needs:
=IF(OR(LEFT(A1,2)="* ",LEFT(A1,1)="*"),A1,"")
This formula checks the first two characters of the cell in column A (using the LEFT function) to see if they are an asterisk followed by a space or just an asterisk.
If either condition is true, it returns the value of the cell. Otherwise, it returns an empty string.
You can then copy this formula down for all cells in the column to return all cells that start with an asterisk followed by a space or just an asterisk.
- 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. - Thanks. That works brilliantly.
