Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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. |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
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. |
#6
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|