Selecting active cells if the cell data start with Z_

Copper Contributor

Hi all,

 

I need some help with this one, please.

I have a list of names in a specific column from which I remove duplicates. This column contains the name of operatives (ex. Adrian Ioan) or subcontractors (ex.Z_Adrian Ioan).

After I removed the duplicates from the column range, I want to create two separate columns: one for operatives and one for subcontractors.

How can I select just the cells starting with Z_ or z_ from given range, using IF function?

Any other suggestion in formulas?

I'm a novice/apprentice in excel and my knowledge is very limited, but learning with pleasure.

Many thanks in advance.

 

1 Reply

Hi Adrian,

 

I would suggest using the Advanced Filter instead.

This tool allows you to filter a data based on criteria and copy it into another location.

 

The criteria should be defined in a cell that has the same header as the targeted column.

 

To extract the names that start with z _, the criteria that you should use can be defined by this formula:

="=z*"

 

After you define the criteria range with the same header as the targeted column, you can use the Advanced Filter which is found in Data >> Sort & Filter >> Advanced.

 

When you open the Advanced Filter, select Copy to another location.

The List range is the targeted column which is $A$1:$A$36.

The Criteria range is the criteria that we just defined in the range $C$1:$C$2.

The Copy to is the first cell of the range that we want to copy the filtered data into.

Advanced Filter - Wildcard Criteria.png

 

Please find the attached file, and try to apply the above instructions in it.

 

Hope that helps

Haytham