How to delete multiple values in a column based on Criteria

**ravi3cha** · 08-02-2010, 11:49 PM

Can any one help how to delete the multiple values in a single column leaving only particular value starting with "EXT".
For Example: I am having an excel in column E,for each row i am having multiple values in E Column like General User;General ALL;EXT-Quanta. I need to delete the all values except the values starting with EXT in that column.

please suggest how to delete the values by using macros or any other procedure..

**teylyn** · 08-03-2010, 12:14 AM

Hi,

you could use a helper column with this formula:

=if(left(E1,3)="EXT",1,0)

copy the formula down, then sort by the new column, select all rows where the new column has a 0 as one block and delete.

**ravi3cha** · 08-03-2010, 05:05 AM

I tried with this formula for finding the values starting with "EXT" from the cell

=MID(B2,FIND("EXT",B2),FIND(";",B2,(FIND("EXT",B2))))

Its working fine when there is only one value starting with "EXT".

In case, we have multiple values starting with EXT in one cell For eg:
EXT-gen;rest;life;EXT-test

Its giving this output:
EXT-gen;

Required output:
EXT-gen;EXT-test

Can anyone help with this formula...

**teylyn** · 08-03-2010, 07:00 AM

You're changing the goalposts during the game.

Please upload a representative data sample in an Excel file and mock up your expected results. Although you say in your first post that you want to act on values that start with EXT, you second post suggests that you want to do something different altogether.

Post a file. Nobody can read your mind.