Free Microsoft Excel 2013 Quick Reference

Find multiple words and replace the adjacent column with one word

I need to find multiple words in one column and replace them with one word in the adjacent one.

I have this headings

Category SubCategory

Each category is related to several subcategories, so I need to find multiple subcategories, and when it finds a match replace the blank space under Category with their corresponding category.

Like This

Category SubCategory
Home Theater Movies & TV
Home Theater Home Theater in a Box
Home Theater another subcategory

now I have only the subcategories, I need to fill the categories with the macro or script.

Post your answer or comment

comments powered by Disqus
I want to find an instance of a word in one column......and replace whatever is in the adjacent column with a word.
Does anyone know how to do that?
I'v ask everyone here locally at the office, but none knows.....

Is possible to have a macro that look at a specific
column (A: A5000) and search for every fifth occurrence
for the word 'TOTAL" and replace the first total with
total1 (Eg A115. no reference to the cell but the word
total). and then create a blank rows of cell and enter
below that same column (A116) vacation1, (A117) sick1
etc. Then on the 10 occurrence of total replace with
total2 (Eg: A300) below that same column create a blank
rows and enter vacation2 (A301), below create a blank
rows SICK2 (A302) I am working on some agent activities
and I will prefer to use a macro since I will be running
the same data daily. I will prefer searching for the
fifth total and no reference to a particular cell number.
Thanks a lot. You guys have been extreme helpful.

Example. Macro search for 5th total on column A. when
found replace with total1. say fifth column found in
A115. if total1 is in A115 then create a blank row in
A116 and enter Vacation1 and create blank cell in A117
and enter Sick1.

Macro search for 10th total on column A. when found
replace with total1. Say 10th column found in A300 if
total2 then create a blank row in A301 and enter
Then create a blank row in A116 and enter Sick2. I
have recorded some macro before but I have Zero idea in
writing macro. So any help will be grateful.

Hey guys, I was wondering if anyone knew 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". Thanks.


I want to find and replace the Date format in word, if there is any macro coding for this. I try to using the following codes

With Selection.Find
.Text = " 7/"
.Replacement.Text = " 07/"
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
End Sub

But "7" is relates to month, so if there is any code for change the numeric character to specific date format as "mm".

For ex :

BUARDC 04294008295 010321 6000 7/17/2010 0000354652 000224

The above is the example data , in that month needs to added a leading zero.

Thanks in advance


I would like to find and replace the styles in the word document.

For instance I have 4 paragraphs in a word document with all the paragraphs denoting to "Normal" style. The first paragraph is set to bold.

When I try to find and replace the paragraph style from "Normal" to "Ram" ("Ram" is an user defined style) all my paragraphs are set to the style "Ram". But my first paragraph still in bold and the style is showing as "Ram".

But when I place my cursor in the same paragraph and reapply the style "Ram" then it is getting converted to the exact format of the style "Ram". But why it is not working for the first time.

Your answer would be highly appreciated.



Find and replace the text in notepad

Excel has 5 columns
Say A1 has values - Test1 ,B1-Test2,C1-Test3,D1-Test4,E1-Test5
In the next row I am entering values in A2-NewTest1,B2-NewTest2,C2-NewTest3,D2-NewTest4,E2-NewTest5

Now for our test ,all the excel row 1 values are available in a notepad file
I need to have a macro in excel which will replace the row 1 values in notepad with row2 values from excel.

end result will be Test1 will be replaced to NewTest1,Test2 to NewTest2 ..and so on in notepad file

Hi there,

I have a little probem that i cant seem to figure out.
For explination purposes lets say i have two worksheets.
Sheet1 has the value of a whole range of stock (over 35000 items) all with unique codes.
Sheet2 has the new values for stock but only for 34000 items so i cannot completely replace sheet1 with sheet2, i need to find a way to match the unique code from sheet2 with sheet1 and transfer the new information from sheet2 over to sheet1.

Sheet1 Sheet2
123 Bike Red 123 Bike Blue
124 Bike Green 124 Bike Yellow
125 Bike Black 125 Bike Pink

I need to replace all the values in sheet1 with the new values in sheet2 so bacically i need an advanced find and replace that matches the value in column 1 and replaces the entire row with the new information.

Thanks in advance guys!

Hi, I’ve been having a bit of an ongoing nightmare with a find and replace on a csv file saved as an excel spreadsheet.
I have some html code (well quite a lot really) in the cells of a column named description. An example of which I have included bellow. To cut a long and painful story short I need to fin and replace alt=” with alt=”” but the cells also contain alt=”something here”.
Not only can I not work out how to write the find and replace for this, I don’t even appear to be able to do any find which highlights the part of the cells contents I wand to find, it jus highlights the cell. Also when I try a find and replace of any type with a word I know to be in the cell I get cannot find the data you’re searching for message.(I only have the formulas option in the Look in dropdown box).
I would greatly appreciate any kind of help with this as it may soon send me crazy.

Thanks for any help.


Men’s 100% cotton cross fire denim jeans.               


[ekm:addtocart][/ekm:addtocart]              [img]/ekmps/shops/rajan/resources/Image/sizeGuide.jpg[/img]              *      
              ****linktext='Email to a Friend';
              ****message='I found this and thought of you.';
              [/ekm:emailtofriend]                            *                    


  Additional Information  

-  Care Instructions  

*****Care codes explained                                                Take a look at these other bargains.                
[img]/ekmps/shops/rajan/resources/Image/MSS-53STAR-SMALL.jpg[/img]                                                Show me    
                                     Show me                                           Show me                               
           Show me                                

how to filter multiple value and replace with one value?
i have following code which change value but one at a time is that possible to in put multiple value to change ?
my code is like this

PHP Code: 
Sub ChangeValue_4() 

    Dim r As Range, rng As Range, s As String, c As String 
    Dim LR As Long 
    With Worksheets("Report") 
        LR = .Range("G" & .Rows.Count).End(xlUp).Row 
        Set r = .Range("G5:G" & LR) 
        s = InputBox("What Value to find and change in Column G ?") 
        c = InputBox("change with Value") 
        .Range("G5:G" & LR).AutoFilter Field:=1, Criteria1:=s 
        On Error Resume Next 
        Set rng = .Range("G6:G" & LR).SpecialCells(xlCellTypeVisible) 'if no items found
        On Error Goto 0 
        If rng Is Nothing Then 
            MsgBox "No records found" 
            .AutoFilterMode = False 
            Exit Sub 
        End If 
        rng.Offset(0, 0) = c 'this changes Column G visible cells
        rng.Offset(0, 1) = Round(c * 0.88, 2) 'this changes Column H
        rng.Offset(0, 2) = Round(c * 0.12, 2) 'this changes Column I
        rng.Offset(0, 3) = c 'this changes Column J
        rng.Offset(0, -1) = c 'this changes Column f
        .AutoFilterMode = False 
    End With 
End Sub 
in this code i need to filter the value in column G as suppose
50.81,49.85,42.65,65.89 (this value input by user)

and change to like say 45.99 (this value also input by user ) to all four value in column G and do the rest so i don't need to run the macro more time and there by save some time.
i hope i explain my problem
here is the sample wb

So here is my delema. I have a product sheet of like 60,000 products all fall in a couple hundred different brands. Right now the brands are abbreviated and I have another excel which includes the abbreviated word and then in the next cell the full word.

How do I mass find and replace the associated abbreviation with the full word? Any help would be greatly appreciated. I have attached sample data.

Thanks to anyone who can enlighten me.

I have a worksheet of invoices, Rows 7 thru 1000. The invoice number is in column A.

On Row 2, I have all data for an invoice to be updated to the original invoice, which is somewhere in Rows 7 thru 1000. I want to find the invoice number in A7:A1000 and replace the entire row with Row 2.

My code, shown below, is not working. It replaces all my formulas in Row 2 with their values. I run the macro from "Invoice Update" ws; the data being updated is in the "History File" ws; control is returned to the "Invoice Update" ws. What is wrong with my code?

    Dim c As Range 
    Sheets("History File").Select 
    For Each c In Range("A7:A1000") 
        If c.Value = Range("A2").Value Then 
            ActiveSheet.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
            :=False, Transpose:=False 
            ActiveCell.Offset(1, 0).Select 
        End If 
    Sheets("Update Invoice").Select 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I've got a table of products that have been ordered, to the right I've got the date which that product was last ordered. This table is a pivottable of a database. I want to be able to update the relative product on the database with the date last ordered from the table. I can't copy and paste the whole ordered column as I want to retain the values that haven't changed.

I was hoping for help on some code to find the product (by name) in column A of the database and replace the value in column E of the same row with the value of the date last ordered on the pivottable

Well, having spent pretty much all day on this "little"
project that sounded so easy originally, I finally found
RegExp & its wonderful pattern-matching capabilities. And
I finally found enough help on the topic to put something
together that works, even if I'm not completely sure how.

>-----Original Message-----
>Ok, here's a "fun" one...
>Running Excel 97.
>I have 2 files. The first file is a 12,000 line
>that has a bunch of old numbers in it that need to be
>replaced. The second file is a list of over 7,000 old
>numbers and the new ones that replace them.
>The partsbook is actually a text file, but I've also
>imported it into Excel, so I could use either format for
>this. The first problem is that each line is 558
>characters long. The second problem is that there's
>really no good way to split it into columns, since there
>are multiple line types and each type has a different
>My first thought was to loop through the replacement
>searching each line of the partsbook for each number in
>the list. It would work fabulously if I had a couple
>extra years and a spare computer....
>The old part numbers are in the format #.###.####.# or
>What I'd like to do is search each line of the partsbook
>for anything that matches those patterns, then do a
>lookup to find the replacement number and replace the old
>number with the new number. But I can't figure out how
>do a pattern matching search AND capture the resulting
>string AND replace it.
>Any ideas?

I am altering REPLACE functions for different parts of my data. This
works OK, but it would be more efficient if I could apply one function
to all of my data.

Column A
R1 volume measured by...
R4 mass times...
R11 weight is related...
R12 height
R100 distance and time
R124 force

I want to place a colon after the numbers; e.g., R1: volume...; R12:
height. If I knew how to find the first occurrence of a space, I think
I could just find those spaces and replace them with a colon and a
space. My work-around is to use REPLACE this way:

I apply this formula to cells with one-digit numbers:
=REPLACE(A1, 3, 1, ": ")

Then I slightly change the formula for cells with two-digit numbers:
=REPLACE(A1, 4, 1, ": ") ... and so on.

Can someone show me a better way of doing this? Thank you!

marlea's Profile:
View this thread:

In cell (P4) there is a formula that gives a number in C-mils (example "89268") based on several manualy inputer numbers.
I have a chart that has (AT4) through (AT33) each row of this column has a higher C-mill number example (AT14) = 83690 and (AT15) = 105600. in the column to the right of (AU) is a I want to reference back to (Q4).

such as the number in P4 is =>(AT14) and (AT14)

Hello All!

Well, as much as I hate to admit it, my previous attempts at coding have resulted in a a swirling mixture of frustration and sadness. So, like any saddened employee, I've retreated to the internet to try and see if anyone might be able to lend a hand.

What I've been trying (and failing quit splendidly) to accomplish is to create a macro that can go through a list of product names in column B on one spreadsheet (Called Productions), find a matching value(s) in Column B on a seperate spreadsheet (entitled Codex), and copy the complimentary Column A from 'Codex' value that is directly adjacent to the Column B value found into Column A on 'Productions'. One of the major issues I'm running into is that on 'Productions', the column B value could have multiple matches in 'Codex'. Is there any way to design a macro that could copy the values and, if presented with more than one variable in 'Codex', just copy all of the values over into Column A of productions?

As much as I hate to admit it, I am in need of assistance! Thank you for any advice you might be able to give!

Hi. I have a table of about 25 columns and 250 rows of data, wich is the result of various formulas like vlookup. I need to find and replace the #N/A output of the formulas for a blank space, i need nothing to appear in those celds. When using the find function i cant get it to work, of course, isnce is the output of the formula. I cant also copy and paste as especial (values only) so i can do this, since i need the formulas to stay.

Plz i need your help!

Thanks in advance to all of you!


I am altering REPLACE functions for different parts of my data. This works OK, but it would be more efficient if I could apply one function to all of my data.

Column A
R1 volume measured by...
R4 mass times...
R11 weight is related...
R12 height
R100 distance and time
R124 force

I want to place a colon after the numbers; e.g., R1: volume...; R12: height. If I knew how to find the first occurrence of a space, I think I could just find those spaces and replace them with a colon and a space. My work-around is to use REPLACE this way:

I apply this formula to cells with one-digit numbers:
=REPLACE(A1, 3, 1, ": ")

Then I slightly change the formula for cells with two-digit numbers:
=REPLACE(A1, 4, 1, ": ") ... and so on.

Can someone show me a better way of doing this? Thank you!

I'm relatively new to VBA and am probably trying to run before I can walk but I'm hoping someone will be able to help me figure it all out.

I have a workbook with 4 different sheets, Alpha, Beta, Gamma, Delta and in each sheet there are 5 different columns, Account Number, Name, Status (Stopped / Off Stop), Date Placed on Stop, Date Taken Off Stop.

I have created a userform for entering new data. In this Userform I have a ComboBox for the sheet names, a TextBox for the Account Number, a ComboBox for the Status, a TextBox for Name, a TextBox for the Placed on Stop Date and a TextBox for the Taken Off Stop Date.

I have part of my Userform entry working. When "Stopped" is selected in the ComboBox the information is inserted into a new row in the relevant sheet.

What I can't get to work is:
When "Off Stop" is selected I want it to search, in the relevant sheet, for the Account Number that was entered in the TextBox in the Userform. I don't want it to do anything with the account number it finds but rather replace "Stopped", that is in the same record and in the Status Column, with "Off Stop". I also want it to insert the date that was entered in the "Taken Off Stop" TextBox in the Userform, into the "Date Taken Off Stop" column , and then I want it to cut the entire row and paste it in row 4.

Is this possible? And how do I go about it?

The last column in my word document (table) contains both text and numbers
(date) in the same row. When copied to excel, the date is put in a different
row from that of text. This creates an unnecessary row just for the date. I
would like excel to split date into a different column of the same row. I
could have wanted word to split date from text, but the word document is not
mine, I cannot alter it's presentation.

Hello again!!

Does anyone know how to do the following?

I want to find the last row in the data field in the adjacent column to my left, then put in ascending/descending (depending on which way you look at it!) in from the bottom up to the top so row 1 would be 1, then 2 would be 2 etc etc

Or if not i can put in something at the bottom of the rows in that column by doing and offset then maybe you could tell me how to put the numbers in.

thank you.

I'm nearly done, hopefully there wont be many more questions!

Oh and when doing this can someone tell me is there a way to to simply move from my active cell to the column next to me, so i can find the last data cell in a column then jump over to the next column which would be empty then put something in it and copy up.

need help to search a column, find a letter, and return the number at the top of the row, the letter appears in.

for example, i do schedules.. say. 50 people, my form…
I want it to search the row next to employee. When it finds a " B ".
Input the time at the top of the column.

trying to add an attachment.. but not sure it worked here.

hope this is clear


I have 2 spreadsheets, 1 sheet has a list of suppliers that match up with the corresponding product. For instance the sheet looks like this ( ID Supplier Name Product Name)

What im trying to do is take the suppliers list and replace the supplier name with the id number from the supplier list.

Two different sheets i need to basically find and replace the supplier ID number from the supplier sheet to the product supplier column.

Is this possible?

Basically just that.

I need to find the last column with a value greater than 0, or the first column with a value of

No luck finding an answer? You could always try Google.