Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting all data in cell if value starts with 3 particular letter
I have a very large list of data which includes a column containing codes.
The codes might be just numbers or they could have letters in them as well. If the code for the particular item starts with, lets say, ASD I want to delete all the data in the cell. How do I achieve this? I am a sophisticated user of Excel but do not do programming |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting all data in cell if value starts with 3 particular letter
Select your cells and run:
Sub cleaner() For Each r In Selection If Application.WorksheetFunction.IsText(r.Value) Then If Left(r.Value, 3) = "ASD" Then r.Value = "" End If End If Next End Sub -- Gary''s Student - gsnu200753 "Mary Ann" wrote: I have a very large list of data which includes a column containing codes. The codes might be just numbers or they could have letters in them as well. If the code for the particular item starts with, lets say, ASD I want to delete all the data in the cell. How do I achieve this? I am a sophisticated user of Excel but do not do programming |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting all data in cell if value starts with 3 particular letter
Try Edit Replace with Find: ASD*
Leave replace box blank and in Options, check Match Entire Cell Contents. "Mary Ann" wrote: I have a very large list of data which includes a column containing codes. The codes might be just numbers or they could have letters in them as well. If the code for the particular item starts with, lets say, ASD I want to delete all the data in the cell. How do I achieve this? I am a sophisticated user of Excel but do not do programming |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting all data in cell if value starts with 3 particular letter
Depending on the size of the list, you could apply a filter, then select
custom, then sort by Begins with and type in ASD. Then, all the cells starting with ASD would show up, and you could select them all, and delete them. If its a long list, a quick way of doing that would be, highlight the first in the list, press buttons Ctrl+Shift+ Down Arrow. That will take you to the bottom of the list. "Mary Ann" wrote: I have a very large list of data which includes a column containing codes. The codes might be just numbers or they could have letters in them as well. If the code for the particular item starts with, lets say, ASD I want to delete all the data in the cell. How do I achieve this? I am a sophisticated user of Excel but do not do programming |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting all data in cell if value starts with 3 particular le
Thanks for your reply. My experience was Find/Replace did not recognise the
* wildcard. I'm using one of the other solutions - with the Autofilter. "Lori" wrote: Try Edit Replace with Find: ASD* Leave replace box blank and in Options, check Match Entire Cell Contents. "Mary Ann" wrote: I have a very large list of data which includes a column containing codes. The codes might be just numbers or they could have letters in them as well. If the code for the particular item starts with, lets say, ASD I want to delete all the data in the cell. How do I achieve this? I am a sophisticated user of Excel but do not do programming |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting all data in cell if value starts with 3 particular le
Thanks for your reply. This is the solution I've used.
"zxiqlpixs" wrote: Depending on the size of the list, you could apply a filter, then select custom, then sort by Begins with and type in ASD. Then, all the cells starting with ASD would show up, and you could select them all, and delete them. If its a long list, a quick way of doing that would be, highlight the first in the list, press buttons Ctrl+Shift+ Down Arrow. That will take you to the bottom of the list. "Mary Ann" wrote: I have a very large list of data which includes a column containing codes. The codes might be just numbers or they could have letters in them as well. If the code for the particular item starts with, lets say, ASD I want to delete all the data in the cell. How do I achieve this? I am a sophisticated user of Excel but do not do programming |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Deleting all data in cell if value starts with 3 particular le
Thanks for the reply - I don't do programming so am going with another of the
solutions offered. "Gary''s Student" wrote: Select your cells and run: Sub cleaner() For Each r In Selection If Application.WorksheetFunction.IsText(r.Value) Then If Left(r.Value, 3) = "ASD" Then r.Value = "" End If End If Next End Sub -- Gary''s Student - gsnu200753 "Mary Ann" wrote: I have a very large list of data which includes a column containing codes. The codes might be just numbers or they could have letters in them as well. If the code for the particular item starts with, lets say, ASD I want to delete all the data in the cell. How do I achieve this? I am a sophisticated user of Excel but do not do programming |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I merge two cells without deleting data from the cell? | Excel Discussion (Misc queries) | |||
Deleting cell data without deleting formula | Excel Discussion (Misc queries) | |||
deleting various rows of cell data throughout the master list tha. | Excel Discussion (Misc queries) | |||
Moving cell data and separating or deleting | Excel Worksheet Functions | |||
How can I make cell data automatically shift up when deleting | Excel Discussion (Misc queries) |