Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 31
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,058
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 272
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 4
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 31
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 31
Default 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   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 31
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I merge two cells without deleting data from the cell? Batmans_Wife Excel Discussion (Misc queries) 50 April 3rd 23 10:54 AM
Deleting cell data without deleting formula Tom Hall Excel Discussion (Misc queries) 4 October 29th 06 05:07 PM
deleting various rows of cell data throughout the master list tha. Flip Excel Discussion (Misc queries) 1 August 8th 06 03:14 PM
Moving cell data and separating or deleting jonhunt Excel Worksheet Functions 6 June 13th 06 07:11 PM
How can I make cell data automatically shift up when deleting Orangepegs Excel Discussion (Misc queries) 4 February 27th 05 12:11 AM


All times are GMT +1. The time now is 08:14 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"