Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 648
  • Last Modified:

EXCEL 2010 HOW TO REMOVE DATA BASED ON NUMBER OF CHARATERS

I have an Excel sheet that I need to do some custom filtering, here's what I am trying to do:

I have a report that has specific data in Column A, In column D I have stacks of numbers in each cell that are associated with the number in Column A.

I am trying to remove the sets of numbers from Column D  that I don't need. The easiest was to separate these is by number of characters. The numbers I need to keep are US####### (US and 7 digits).

I have tried filtering, and it only reads the first number in a cell, if it doesn't meet the filter it's not shown. Even if the number below it (in the same cell) matches the filter.

So here is the big question: How do I tell Excel to look in column D and only keep all instances of US#######, while removing anything else. And doing do to the entire cell, not just the first entry of the cell.

I hope I made sense, my brain hurts at this point!
0
Majo2469
Asked:
Majo2469
4 Solutions
 
sshah254Commented:
Sort the data based on column D.  Select everything that is not US and delete it.  Should be simple.  Why are you making it complicated?

Unless you are doing it with a macro ...

Ss
0
 
Rob HensonIT & Database AssistantCommented:
If you are using Auto Filter you can use the Custom option and choose Equals to and enter US??????? into the entry box.

This will show all entries that begin with US and have specifically seven characters after.

If you want to show all rows that don't meet that criteria then use the dropdown in Custom filter for "does not equal" and enter the same.  You can then delete the rows that are shown.

Cheers
Rob H
0
 
Pancomp60Information Systems ManagerCommented:
If I understand you post correctly, I think I found an answer for you at Mr. Excell.com, posted by Tom Urtis http://www.mrexcel.com/forum/showthread.php?t=2723

While this particular post worked when I tested it on Excel 2007, it should also work for you.  I don't have Excel 2010, but the steps outlined below (based on the above link) should help you in the right direction.

Excel 2007 steps:

1. Select the column to be filtered
2. On the Data tab, select Filter
3. Left-click on the Filter button at the top of the column.
4. In the submenu select Text Filters Custom Filter...
5. In the Custom AutoFilter window, in the left box select "equals". In the right box type in ????????? or as many as you want for the number of characters to filter.  9-? will filter (show) only rows with 9 characters in the cell, which in your case should be any cell that contains US followed by 7 numbers.

I hope this helps.
0
 
leonstrykerCommented:
Use this formula in Column E and copy to each row with coresponding data in column D

=IF(AND(LEFT(D1,2)="US",LEN(D1)=9),1,0)

Filter on E for 1s.
0
 
TracyVBA DeveloperCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Who's Defending Your Organization from Threats?

Protecting against advanced threats requires an IT dream team – a well-oiled machine of people and solutions working together to defend your organization. Download our resource kit today to learn more about the tools you need to build you IT Dream Team!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now