How to delete rows with values that END with...

[blackice]

so here is my problem, i have a long list of IP addresses in an excel column, they all either end with .1 or .3, i want to be able to get rid of any ip address in the list that ends with .1 but when i select the column and choose filter by ends with and put the value (i have tried .1, 1 *1 ".1" and "1") and all it does is remove every value, the column looks like this

10.10.5.1
10.10.5.3
10.10.6.1
10.10.7.1
10.10.7.3
10.10.8.1
10.10.8.3
10.10.9.1
10.10.10.1
10.10.10.3
10.10.11.1
10.10.12.3

and i want to remove all cells that have an IP address that ends in .1 so that it would look like this:

10.10.5.3
10.10.7.3
10.10.8.3
10.10.10.3
10.10.12.3

i figured the ends with filter would work but i dont know why its not doing what i expected it to, there are several thousand cells and it will be impossible to delete them one by one, if someone could show me how i can just get rid of any cell that ends with .1 i would greatly appreciate it.

Thanks in advance

 

[JimM]

The filter by Ends With .1 should work, have you got any empty spaces at the end of the IP adresses ? If you have, use Find and Replace (Ctrl-H) to get rid of them and the filter should then work

 

[blackice]

Ok i think that is the problem do see blank spaces at the end of the addreses, but to use find and replace, would i just enter a space in top Field and nothing in the replace with field? i tried that and it couldnt find any data to replace

 

[Smitty]

Welcome to the Board!

You could use a helper column:

=RIGHT(TRIM(A1),2)

HTH,

 

[blackice]

That did work but apparantly if i copy the data out of the new Trim column and past special "values" to a new column, it adds the space back in

 

[JimM]

Yes, if you highlight the range with the IP addresses in and then use the find and replace (a space in the top field & nothing in bottom field) it should remove the empty spaces

Alternatively you could create another column and use the =TRIM() function

 

[blackice]

So the find and replace for whatever reason did not work, it said couldnt find anything to replace, when i did the trip option in the new column and then tried filtering out the .1 in the second column, it didnt work because i think it looked for values in the reference column also, so it didnt filter properly. So i tried copying the values out of the new column (trim) column and pasting in a new column and it added the space right back

 

[blackice]

So i found a solution, i used a hex converter and converted all the values to hex and then did a find and replace on the 0xa0 and then converted back to ascii and it worked! thanks for all the help!