Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Advertisements:










Search And Replace Replace The Whole Cell


I have over 16000 cells with different information in them, each cell is unique. There are parts of these cells that have similar information.

I am looking to search part of the cell, and replace the whole cell with my new description, so I can run pivot tables on it.


View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Search And Replace: Loop To Look Up Xxx And Replace It With The Values Consecutively In Row C
I have values in row A and I have values in row C. I want to create a loop to look up xxx and replace it with the values consecutively in row C. Look at example for a better Idea. I found a way to find and replace, but I not sure how to use it with qoutes. I was thinking maybe I dont even need row A and just supply a list and excel could have the chunk of data in the code itself.

View Replies!   View Related
Search And Replace Formula For A Cell
I am looking for a formula for a cell that will do the following:
from :

robert johnson hot dog

To this
robert+johnson+hot+dog

I would like it only to change the space between the words to a + sign.

View Replies!   View Related
Search Cells And Replace Text In That Cell
Search multiple cells for certain string and then replace the text of the entire cell with new text. For example the text may contain:

"A Chestnut Leather Satchel Binocular Case "They'll Fight Over When You're Dead" (Binocularcase-SL-CH)"

I want to search for SL-CH and once that is found I want to replace the contents of the entire cell with the following: Satchel Chestnut (SL-CH)

View Replies!   View Related
Search & Replace - Preserve Cell Format
I'm using the code below to do a simple search & replace. The code is working fine.
My problem is after the search & replace is done, certain cells that contain numbers look weird with decimal points & plus signs similar to Format Cells - Scientific. How do I prevent these cells from looking 'weird' in my code?

I've attached a workbook with 2 sheets called Before & After. You can see the 'weird' cells in After.

Sub Macro1()
Cells.Replace What:=": ", Replacement:="", LookAt:=xlPart, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
End Sub

View Replies!   View Related
Find, Replace With And Then Replace Adjacent Cell
I am trying to create a macro where it finds a a certain word in a column for example C. What i want it to do is find anything that says FWD_EUR and then replace that cell (e.g C2) with CASH_EUR_FWD and after it has done that it replaces the adjacent cell (e.g. D2) with EUR_FWD. I then want this to do the same with FWD_USD to CASH_USD_FWD and adjacent cell to USD_FWD.

View Replies!   View Related
Search Across Columns By Row & Replace Specific Cell Contents
I have created a desk planning spreadsheet in excel. Each week a mailmerge sends out an email to everyone in the office asking them to confirm when they will need a desk in the next week.

As the normal employee doesnt get involved in desk planning I want them simply to state whether they will be in the building or not by entering "GH" (our building) in a cell under each day in the email they get sent.

When the reply comes in we select the cells from the email and then paste them into the desk planning spreadsheet.
-----

WHAT I NEED TO DO:
Select some cells (must be in the same row - that we have just pasted in).
Press a button on the Worksheet labelled "Auto Assign Desk", which does this:-
For each cell that contains "GH", replace with the right-most cell to the left of the selection that starts "GH_"... (this is the start of a unique desk reference (GH_1_1, GH_1_2,etc.)
-----

This will assign the employee to the desk they were sat at last, when they are in the office next week.

View Replies!   View Related
Replace #VALUE With <nul> On SEARCH
=IF(SEARCH($C$2,'705334-0707-0408'!C3)>0,ROWS($1:2),"")

Subsequent data selection also results in #VALUE...

=IF(ROWS($1:1)>COUNT($A$5:$A$1000),"",INDEX('705334-0707-0408'!B:B,SMALL($A$5:$A$1000,ROWS($1:1))+1))

In sheet one I have a list of bank transactions. I want to find all transactions matching a specific search string (in $C$2). $C$2 is compared with data in column B of sheet 705334-0707-0408.

How do I get a nul value in column A (=IF(SEARCH($C$2,'705334-0707-0408'!C3)>0,ROWS($1:2),"")). I've tried to compare the result to #VALUE, and tried NOT and a few other combinations, but can't get it to work.

I cant use the contents of a different column; there are only date, details and amount. None of date or amount influence, or are influenced by the details in column B.


View Replies!   View Related
Vba Search And Replace
I am looking for code that will look for text in column A and input text into column C. for example:

if column A contains "scat" then input "scatter" into column C

if column A contains "fab" then input "Fabric" into column C

View Replies!   View Related
Catching Errors When Using The Find / Replace Function / Replace All
Need assistance with the code for catching errors when using the find / replace function in excel? In particular, I am trying to write code to break to an error message when the value or string searched for isn't found in the find / replace. At the minute I have just copied the standard code using a macro and all this does is return a message box saying X entries replaced.

View Replies!   View Related
Using Wildcard With Search And Replace
I have numerous formulas that use a macro with an indirect cell reference, for example:
INDIRECT(macro(A21))

I am moving these formulas to a different workbook and I need them to still refer to items in the old sheet. I accomplish this by using this formula:

INDIRECT(CONCATENATE("OtherSheet.xls!",macro(A21)))
This works. However, I need to do a global search and replace for all of these formulas without deleting my indirect cell reference. I could replace
macro
with
CONCATENATE("OtherSheet.xls!",macro
but that would leave me short a ).
Is there a way to use a wildcard to accomplish this? It does not seem that replace
macro(*)
with
CONCATENATE("OtherSheet.xls!",macro(*))


View Replies!   View Related
Search And Replace Code
How can I reduce the code to perform this task ?

View Replies!   View Related
Suppress Zeros In A Pivot Table (search Out Any Zeros And Replace With A Blank Cell)
with the data in the attached sheet, I create several different pivot tables that need show the count of the information in the columns M:DU. My issue is that the data is sent to me from a third party and the columns contain zeros that cause the counts to inflate.

What I would like to be able to do is run a macro that will search out any zeros in M:DU and replace them with a blank cell.

Unfortunately the number of rows increases with every monthly reporting cycle so the macro would need to be able to accommodate for that.

View Replies!   View Related
Search For A Value If Found Go Up One Row And Replace First 4 Digits
I am looping in Excel to find "NUMBER OF EMPLOYEES". IF found I want to go up one row and replace the first 4 digits with " 9ZZ". The Line 1 data will alway be different so I just want to replace the first 4 digits. I don't want it to effect the rest of the line.

Example:
Line 1 - 100 6300 BRCH TOTALS
Line 2 - NUMBER OF EMPLOYEES

RESULTS NEEDED:

Line 1 - 9ZZ 6300 BRCH TOTALS
Line 2 - NUMBE OF EMPLOYEES

View Replies!   View Related
Search And Replace Text In A Column
I want to replace all blank cells in column A with the word "BLANK" but my problem is that I need to tell Excel to stop searching for blank cells in column A once and only once column A is equal to the last data row in the column named "Product Number". I tried this macro but, naturally, it replaces all the empty cells in the WHOLE COLUMN.

I will always have a header column in row 1. The data is below that (data will always start at row 2).


Columns("A:A").Select
Selection.Replace What:="", Replacement:="BLANK", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
How can I change this so that the find/replace macro searches all the way down equal to the last data row in the "Product Number" column?

Below are 2 examples. Example 1 is pre macro and example 2 is post macro.


...............................

View Replies!   View Related
Search And Replace All Blank Cells
I would like to create a macro to search all blank cells within a certain range in multiple worksheets to replace it with a certain blank cell. Essentially i would like to use a special values function to do this.

View Replies!   View Related
Search And Replace Special Characters
I have a worksheet that I produced from optical character recognition, and there are a lot of funky characters that I need to get rid of. One is a line break or carraige return (I assume it's the same character that I could insert by entering ALT+ENTER). How do I search for this special character to replace it? How do I identify what that invisible character is?

View Replies!   View Related
Unwanted Character, Can Not Search And Replace
I have a sheet that I often have to edit and import into SQL database.

I have no control over the format of the sheet given to me and it seems that something has changed causing me the following problem.

When I try to import the Data itís showing ten times as many items than are actually there.

The problem I think is in a text column. Iíve tested this by using an old column from a previous sheet and pasting this into the new sheet and I can import it ok.

What I seem to have is a rectangular box character but Iím not sure what it is.
I can copy and past it into Notepad as it is, but pasted into Word it seems to be a carriage Return.

So whether itís an actual charter or not Iím not sure.

Are there any tools out there so I can hover over characters in Excel to see what it is! So at least I can be sure what Iím trying to Search and Replace?

View Replies!   View Related
Search & Replace Date In Date And Time Cell
I have several hundred rows of date/times which I need to modify just the date (for example 08/23/2007 11:00 to 08/15/2008 11:00). Each row may have a different time so it is just the date I'm targeting for change.

If I manually use the replace dialog, replacing 08/23/2007 with 08/15/2008 works just fine. However, if in code I attempt to use the Cells.Replace function, it does not locate any data to change.

I have found that if I search for the string 8/23/2007, the dates are located and changed. Only when I attempt to find the fully formatted date 08/23/2007 does the function fail.

View Replies!   View Related
Search For Specific Text In A String And Replace
I have a text such as:

fdfs&s_kwcid
sfsd&s_kwcid=dfsads
&dfsdaf&dsafdsf&s_kwcid=
fdsf&dasfsdf&s_kwcid=dsfasfsdf

I want to do is search for "&s_kwcid" or anything containing "&s_kwcid" and replace it with blank. So above would then read:

fdfs
sfsd
&dfsdaf&dsafdsf
fdsf&dasfsdf

I tried =IF(SUM(COUNTIF(E2,{"&s_kwcid*"}))=1,E2,"") but it didn't work. I tried auto filtering, and using contains &s_kwcid* but it didn't filter out results, but find &s_kwcid did find results for text anywhere in string, so I know the problem is there.

View Replies!   View Related
Search/Replace Variables Workbook Formulas
I am using heavily formula based modelling workbooks and I need to know how to search for variables (inputs) in these workbooks as they contain many thousand rows and it would take ages to go through line by line looking for these variables

View Replies!   View Related
Remove Commas From All Cells, Search & Replace Error: Formula Is Too Long
I have a large spreadsheet, within which i am trying to remove commas from all cells. I get the error 'formula is too long' when I carry out the search. Some of the cells are >1024 characters in length and contain dates, text etc.

View Replies!   View Related
Replace, Copy Then Replace Again
I used the following code to replace " characters to !%. Then I copy the selected cells, and replace the !% back to "-es. The copied cells contain the "-es. When I comment the second replace, the function is working correctly, the copied cells contain the !%. What's the problem of my code?

Selection.Replace _
What:="""", Replacement:="!%"

Selection.Copy

'Selection.Replace _
' What:="!%", Replacement:=""""

View Replies!   View Related
Search & Replace Real Dates & Text Dates
I have a macro that searches and replaces dates (both text and date formats) in all files in a folder. It works if I initiate it from the VBA Editor, but not if I initiate it from the file.

It seems that the worksheet is not activating. Please take a look and let me know what I can try.

Things I have already tried: Window methods, such as minimizing the workbook that contains the macro, activate next, etc. Also putting the macro in Personal.xls and retrieving the values from the other file.

Where it hangs is right after the first workbook in the folder is opened. I tried to get it to select A1 in the active sheet but that cell is not selected in either the macro workbook or the newly opened workbook.

Also, if there is an easy way to exclude my macro file (name contains ZZZZ) from the FileSearch............

View Replies!   View Related
Replace One Row With Another Based On A Cell
I have a table that i would like to update. All my macro needs to do is search the entire first column of a table for a match to cell A1, there will only be 1 match, once the match is found row b's values need to replace the row thats entry in its first column matched cell A1.

View Replies!   View Related
Vba Code To Replace A Cell Value With Another If
I want to search column K for the word "test" and for each match, replace it with what ever cell value is in the same row but from column T ?

View Replies!   View Related
Replace Name In Formula With Cell Reference
In the following formula, I would like to replace STARK with the value in cell D33. I've tried a couple of different things using &s but no luck yet. I think I'm just missing something really simple.

View Replies!   View Related
Match And Replace Cell From Another Sheet
Question: I have two sheets named Sheet1 and Sheet2. Sheets looks like this:

Sheet1
A B
1 John Smith
2 Jane Doe
3 Joe Smo
4 Jackie Chan

Sheet2
A
John Smith
Joe Smo
Joe Smo
Joe Smo
Jane Doe
Bruce Lee

Here is the logic: If any cell in Sheet2 Column A = Any cell in Sheet1 Column B, change sheet2 column A cell value to sheet1 column A value (id).


View Replies!   View Related
Replace Part Of A Cell (first Two Digits)
I am wanting to replace part of a cell ( the first two digits) with nothing - blank

eg cell contains 441298871657 and i want it to read 1298871657
I am looking for a function to do this, i have tried using the finf and replace function but if 44 appears somewhere else in the value this will also be replaced.


View Replies!   View Related
Replace Cell Value In Entire Sheet
is it possible to replace the value of all the cells which have value =customer in the entire sheet.

for this first i will have to first search for all those columns which have value = customer and then replace them all.


View Replies!   View Related
Adding And Replace A Number In A Cell
i have a question about adding a number to a cell. This is the problem. Each day we track contacts we made in a spread sheet. how could i, lets say type 2 in a cell that already reads 42 and once a tab away from the cell it would change to 44 (add in the background 42+2=44).

View Replies!   View Related
Find & Replace All Characters In Cell Like #
I need help with a formula (no VBA) that will find all the semi-colons ";" and pound signs "#" in a cell and replace them with this "<br> - ".

Example of Cell A1: ;#Red;#Green;#Blue;#

I started with this:

=REPLACE(MultChoice,SEARCH(";#",MultChoice),1,"<br> - ")

but it only replaces the first character.

View Replies!   View Related
Find And Replace Part Of Cell
As part of a macro i do a find and replace on a range cells that replaces "/1" with nothing (""). Therefore 2/1 becomes 2, what i don't want though is if the de-nominator includes a 1 but not only 1 to keep the whole of the de-nominator, say 8/11 does not want to become 8/1.

View Replies!   View Related
Find/replace Using Symbol For End Of Cell?
Is there a way to define the end of a cell in a find/replace? I.e. I want to find characters like this:

;;[space character][end of cell]

and replace with

[end of cell]

(that is just delete the ;;[space character] bit when it is followed by end of cell)

View Replies!   View Related
Replace 2 Characters Signs In Cell With Another
QUESTION: How do I check to see if a cell contains either "" or "/" and if it does then replace it with "-"? THE REASON: I have a form which needs to be saved in a particular directory with the name "Form 1 - client name - matter name". To do this I have put =("G:UsersCComplianceFile OpeningForm 1 - "&B7&" - "&D42) in Cell A1 and then in VBA added the following macro to a button which saves the file

ThisFile = Range("A1").Value
ActiveWorkbook.SaveAs Filename:=ThisFile

This works ok BUT, if the user puts "/" or "" in either B7 or D42, it won't save and throws up an error because you can't use those signs when saving a file.

View Replies!   View Related
Replace Part Of The Word In Cell
I have a worksheet - sheet 1 like below:

00001 - $2000
00002 - $3150
00004 - $2010
00001 - $1011

I would like to look up customer number (such as 00001) and replace to Peter

sheet2 like:

00001 Peter
00002 Marry
00003 John
00004 Mac

Expect Output:

Peter - $2000
Marry - $3150
Mac - $2010
Peter - $1011

View Replies!   View Related
Using VBA To Replace Data In A Specific Cell
I have a spreadsheet, where I am measuring dimensions for a cpk test. My question is, can I use vba to format the calculated cpk cell so that if it falls below a certain value it replaces that value with whatever I have specified in vba? Was wondering is this is possible.

View Replies!   View Related
Split Cell And Insert (not Replace) The New Cells
I need to make sure that splited cells do not replace content of other cells. For example, if A1 has "SDR232, SDR634", this code will split them and put A1 to be "SDR232" and A2 to be "SDR634". BUT what it does is that it actually replaces whatever I have in A2. Is ther anyway to avoid replacing the content of A2?

Sub tst()
Dim X As Variant
X = Split(Range("A1").Value, ",")
Range("A1").Resize(UBound(X) - LBound(X) + 1).Value = Application.Transpose(X)
End Sub

View Replies!   View Related
Macro Will Not Run Find And Replace With The Contents Of A Cell
I want to have a macro that will look at a few cells say A1 & A2 and then use the contents of the cells say 1Q07 & 2Q07 respectively to do a find and replace find 1Q07 and replace it with 2Q07. I want it to use the contents of the cell and not just the text because the contents of the cell will change each time used. I tride to do it here but I dont have it yet. It doesnt seem to pass the value to the variable and the variable doesnt seem to do the find and replace.

Private Sub Update_Click()

Application. ScreenUpdating = False
Dim quarter As String
quarter = CStr(txtquarter.Text)
txtquarter.Text = CStr(quarter)

Sheets("Income Statement").Select
Dim oneq As Single
Dim twoq As Single
Dim threeq As Single
Dim fourq As Single
Dim fiveq As Single

oneq = ActiveSheet.Range("A2").Value
twoq = oneq - 1
threeq = twoq - 1
fourq = oneq - 4
fiveq = oneq - 5

View Replies!   View Related
Append To & Replace Cell Data By Condition
I want to append / replace data in cells depending on the initial data.

A cell has the value 3AB00456 XYZ
I want to append the value with My
I want to check to see if the 4th & 5th characters = 00
If YES, I want to append the value with My and replace the rest with just the the 1st 3 characters So the final value would be My3AB
If NO, (the cell has the value 3AB02456 XYZ) I want to replace with a hyphen after the 3rd character and then the rest of the cell value. So the final value would be My3AB-02456 XYZ

I have several worksheets each with a column of about 2000 cells. I want to replace the data in the cells and not create another row with the result.

View Replies!   View Related
Replace Words In Cell Matching Those In A List
Im hoping there is an Excel Formula (not VBA Code) solution to this problem.

I understand how to use the Substitute command in Excel, but I would like to be able to provide a list of words and have the Substitute command use that list to replace every occurrence within a given cell with a blank ("").

The twist to this is, that the List of Words will be Dynamic, and thus the formula will need to account for that.

NOTE: the formula should NOT replace parts of words, e.g., if the List Word is "can" and the cell to be evaluated contains "candle" the formula should NOT replace the "can" in "candle" with "". Only whole word matches should be replaced.


View Replies!   View Related
Find 1 Character And Replace Entire Cell
how I could find 1 character and replace the entire cell. For example, it would find "<12", "<3", and "<32" based on "<" and then replaced that entire cell with "N/A".

View Replies!   View Related
Find And Replace Cell That Are Blank With The Dash
I have a row of 2900 single letter (middle initals) however 222 users have no middle inital. this is a password scheme and need 7 digits, without the middle inital i only have 6. so I want to replace all 222 cell that are blank with a dash can this be done without doing each by hand?

View Replies!   View Related
Replace Cells In Selection Same As Active Cell
I need some sort of code to check through a selection of cells in a column and make those cells "zero" whose value is equal to the first cell in the selection. e.g., let's say I have a key value entered in cell e6 = "2.99".

E6 F6 G6 H6 I6 J6
2.99 5.25 2.99 2.00 2.99 5.00

So the macro should be able to check the value in cell E6 (i.e. = 2.99) against values in cells from F6: J6 and any cell having the same value as cell E6 (2.99) should be made = 0. So, in the above example cells G6 & I6 will have "0" value after the code has been executed. I have close to 50 rows going down starting from cell E6. For example, E7 has a value which has to be checked against the range starting from F7 to J7 and so on and so forth.

View Replies!   View Related
Vba Variable To Replace Cell Address In A Formula
when i am doing the coding

may i know if there is a way to replace the cell address $A9 below: ...

View Replies!   View Related
Macro: Replace Multiple Cell Values
I have a datasheet, in which i would like to replace data...
for example, A1 has value of 'a.Male' , A2 has value of 'a.Baby'
and the list goes on...

What i want to do is, find all 'a.'s and replace the whole cell with '1' ...

So after replacing the cells A1 and A2 will be '1' instead of 'a.Male' / 'a.Baby'

If this is any help, i posted somethin i wrote... but it has to be repeated many times... i have a large data set.... I need somethin that will do the job with a single press of a button~

Sub Replace()
'
' Replace Macro
' Macro recorded 2006-11-30 by Jae Heon Choi
'
' Keyboard Shortcut: Ctrl+r
'
Cells.Find(What:="a.", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, MatchByte:=False, SearchFormat:=False).Activate

View Replies!   View Related
Replace Some Words In Cell With Those From Lookup Table
We have a list of typed information (see attached) in which we would like to remove some words and simply replace with a space "". In addition to this: Unfortunately, folks tend to mistype or misspell words, add extra digits, etc, and for those mistakes we would like to replace them with corrections. Therefore, for this problem, we have a Replacement Table and the Subject Data, an example of which is shown in the attachment. I have posted a slightly similar problem on Ozgrid recently:

Replace Words In Cell Matching Those In A List
and in that link, the following UDF was proposed:

Function ReplaceIfInList(rList As Range, rCell As Range, _
strReplacement As String) As String
Dim rLoop As Range

If rCell = "" Then Exit Function
For Each rLoop In rList
If ReplaceIfInList = vbNullString Then
ReplaceIfInList = Replace(rCell, rLoop, strReplacement)
Else
ReplaceIfInList = Replace(ReplaceIfInList, rLoop, strReplacement)
End If
Next rLoop
End Function

This worked great, but at the time i posted this, I did not have the foresight to anticipate the flexibility in what the replacement text would be (the above code always replaces with a ""). THIS new Post is asking the following:

Is there a way to define (or modify the above) User Defined Formula that will analyze the words typed in a cell, which are typically separated by spaces (or on accident, multiple spaces), and essentially use the Replacement Table as a reference for what word to replace with?

View Replies!   View Related
Replace Cell Text Leaving 1 Word
I am looking for a syntax or function wherin i can replace all lines in different cells containing specific string or text in it. e.g. Few cells in sheet contains " This guy is for deployment", other few cells may contain "guy is for core deployment" and other cells may have " project is specific deployment". I want to replace all such lines which contain a word "deployment" with ony one word "Deployment".

View Replies!   View Related
Auto-replace Typed Contents In A Cell With A Formula
I want to set up a sheet macro that will replace the contents of a cell (when a specific number is typed in) with a specific formula.

For instance, when "43" is typed into any cell within specified columns (say columns C and H), it changes to the following formula: =IF(D47="",43,IF(D47<=3030,408,43))(WHERE D47 WOULD REPRESENT THE CELL ADJACENT TO THE CELL WHERE "43" WAS TYPED.)

IE:

If "43" was typed into cell C1, then it would change to the formula: =IF(D1="",43,IF(D1<=3030,408,43))

If "43" was typed into cell C50, then it would change to the formula: =IF(D50="",43,IF(D50<=3030,408,43))

View Replies!   View Related
Find And Replace :: Cell Moving Down In Linked Sheet?
I have a spreadsheet which is linked to other spreadsheets. When I use "find and replace" on this spreadsheet, it causes one of my linked cells to move down one row. Why does it do this? This particular cell has nothing to do with the "find and replace" option.

I have no idea how to stop this from moving this cell down.

View Replies!   View Related
Find Replace :: Find All Occurences Of And Or AND And Replace With And
Range B3:B1000 is text strings. Column C2:C50 is a list of words that I would like to "Find" in Column B and replace with it's lowercase values unless they start off the string.

Example

Find all occurences of And or AND and replace with and
Find all occurences of With or WITH and replace with with.
Find all occurences of Or or OR and replace with or

View Replies!   View Related
Copyright © 2005-08 www.BigResource.com, All rights reserved