Forum Discussion
Selecting active cells if the cell data start with Z_
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.
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.
Please find the attached file, and try to apply the above instructions in it.
Hope that helps
Haytham
