Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 86
Default find text in cell and replace it with part of the text in that ce.

I have data in a cell eg Rewardsmax_dining_729x90_BUSFIN
I want to strip out the _729x90_BUSFIN.

How do I find and replace the contents of this column so that the only text
I keep is

Rewardsmax_dining

each cell in the column has Rewardsmax but each cell has unique text
associated with it. I need to strip out anything now Rewardsmax_dining
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 230
Default find text in cell and replace it with part of the text in that ce.

One way.

Select the cells (or the whole column), then using the Replace feature from
the menu bar, type in a single * in the From What: box and
Rewardsmax_dining in the Replace With: box and click Replace All.

Rob

"jules" wrote in message
...
I have data in a cell eg Rewardsmax_dining_729x90_BUSFIN
I want to strip out the _729x90_BUSFIN.

How do I find and replace the contents of this column so that the only
text
I keep is

Rewardsmax_dining

each cell in the column has Rewardsmax but each cell has unique text
associated with it. I need to strip out anything now Rewardsmax_dining



  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,058
Default find text in cell and replace it with part of the text in that ce.

Hi jules:

Select the cells you want to clean and run:

Sub reward()
Dim s As String
s = "Rewardsmax_dining_729x90_BUSFIN"
For Each r In Selection
If InStr(r.Value, s) < 0 Then
r.Value = s
End If
Next
End Sub

--
Gary''s Student - gsnu200718
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 11,058
Default find text in cell and replace it with part of the text in that ce.

This version will retain only the first part:

Sub reward()
Dim s As String
s = "Rewardsmax_dining"
For Each r In Selection
If InStr(r.Value, s) < 0 Then
r.Value = s
End If
Next
End Sub

--
Gary''s Student - gsnu200718


"jules" wrote:

I have data in a cell eg Rewardsmax_dining_729x90_BUSFIN
I want to strip out the _729x90_BUSFIN.

How do I find and replace the contents of this column so that the only text
I keep is

Rewardsmax_dining

each cell in the column has Rewardsmax but each cell has unique text
associated with it. I need to strip out anything now Rewardsmax_dining

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
Find & Replace in Text Box Ed Excel Discussion (Misc queries) 10 March 28th 07 03:03 AM
REPLACE PART OF CELL WITH FORMATTED TEXT Manju Excel Worksheet Functions 8 December 28th 06 12:42 AM
How do I change replace text in a cell for different text? ronedwards Excel Worksheet Functions 4 August 18th 06 03:31 AM
Find and replace part of a text string [email protected] Excel Discussion (Misc queries) 2 July 10th 06 10:34 PM
Find part of text in column or array Pasmatos Excel Discussion (Misc queries) 0 November 8th 05 11:34 AM


All times are GMT +1. The time now is 03:38 PM.

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"