macro that finds text and keeps only part of it
 

I have a spreadsheet that I'm trying to run a seach on to find a specific
sting of test. I can run the search but, now I want to ake it one step
further. Is it possible to search for a sting of text and then delete part
of the sting and leave some of the text? Then is it possible to do this a
macro? Thanks for the help.

macro that finds text and keeps only part of it
 

Find and replace won't work?
************
Anne Troy
www.OfficeArticles.com

"john mcmichael" wrote in message
...
I have a spreadsheet that I'm trying to run a seach on to find a specific
sting of test. I can run the search but, now I want to ake it one step
further. Is it possible to search for a sting of text and then delete
part
of the sting and leave some of the text? Then is it possible to do this a
macro? Thanks for the help.

macro that finds text and keeps only part of it
 

Find and replace works great for finding the text but I need more. I dont
want to have to manually delete things. I was hoping to use something that
would delete the first 10 characters and keep the rest ( as an example ).
can this be done with wildcards or something? Thanks again.

"Anne Troy" wrote:

Find and replace won't work?
************
Anne Troy
www.OfficeArticles.com

"john mcmichael" wrote in message
...
I have a spreadsheet that I'm trying to run a seach on to find a specific
sting of test. I can run the search but, now I want to ake it one step
further. Is it possible to search for a sting of text and then delete
part
of the sting and leave some of the text? Then is it possible to do this a
macro? Thanks for the help.

macro that finds text and keeps only part of it
 

One way:
Ctrl+H is for Find and Replace
HTH

"john mcmichael" wrote:

I have a spreadsheet that I'm trying to run a seach on to find a specific
sting of test. I can run the search but, now I want to ake it one step
further. Is it possible to search for a sting of text and then delete part
of the sting and leave some of the text? Then is it possible to do this a
macro? Thanks for the help.

macro that finds text and keeps only part of it
 

try this for your string in col E (5)

Sub findanddeletepartofstring()
On Error Resume Next
mycell = Columns(5).Find("abcde").Address
Range(mycell) = Left(Range(mycell), 3)'=abc
End Sub
--
Don Guillett
SalesAid Software

"john mcmichael" wrote in message
...
I have a spreadsheet that I'm trying to run a seach on to find a specific
sting of test. I can run the search but, now I want to ake it one step
further. Is it possible to search for a sting of text and then delete
part
of the sting and leave some of the text? Then is it possible to do this a
macro? Thanks for the help.

macro that finds text and keeps only part of it
 

One way is with a macro:

Option Explicit
Sub testme()

Dim wks As Worksheet
Dim FirstAddress As String
Dim FoundCell As Range
Dim WhatToFind As String
Dim myRng As Range

Set wks = Worksheets("Sheet1")
WhatToFind = "abcdef"

With wks
Set myRng = .UsedRange

With myRng
Set FoundCell = .Cells.Find(what:=WhatToFind, _
after:=.Cells(.Cells.Count), _
lookat:=xlPart, LookIn:=xlValues, _
searchorder:=xlByRows, _
searchdirection:=xlNext, _
MatchCase:=False)

If FoundCell Is Nothing Then
'do nothing
Else
FirstAddress = FoundCell.Address
Do
FoundCell.Value = Mid(FoundCell.Value, 11)
Set FoundCell = .FindNext(FoundCell)
If FoundCell Is Nothing Then
Exit Do
Else
If FoundCell.Address = FirstAddress Then
Exit Do
End If
End If
Loop
End If
End With
End With

End Sub

This is the portion that changed the cell:

FoundCell.Value = Mid(FoundCell.Value, 11)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

john mcmichael wrote:

Find and replace works great for finding the text but I need more. I dont
want to have to manually delete things. I was hoping to use something that
would delete the first 10 characters and keep the rest ( as an example ).
can this be done with wildcards or something? Thanks again.

"Anne Troy" wrote:

Find and replace won't work?
************
Anne Troy
www.OfficeArticles.com

"john mcmichael" wrote in message
...
I have a spreadsheet that I'm trying to run a seach on to find a specific
sting of test. I can run the search but, now I want to ake it one step
further. Is it possible to search for a sting of text and then delete
part
of the sting and leave some of the text? Then is it possible to do this a
macro? Thanks for the help.




--

Dave Peterson