Teach Yourself Excel Lesson Find And Replace - Find And Replace

I am trying to figure out how to make this work. What I want to do is:
I have a speadsheet with 7000 plus part numbers and I want to delete the colors from the part numbers using a macro. What I would really like is for the macro to look for values that are in a range and replace them in the parts list with nothing.

So my parts list looks like this
Sheet1
Column A
08445365800-082 08601223700- 08601223800- 08704236500L0BKP 08704236500L0RXP 08704236500R0RXP

Color Codes
Sheet2
Column A
082
0BKP
0RXP

So what I want the macro to do is remove what ever color codes are on Sheet2 column A from the Partslist on SHeet1 Column A. I thought about removing just the last 3 characters, but that doesn't work because I parts without colors. Any help would be greatly appreciated. I will need to be able to add more color codes to Sheet2 as I go.

I've been scouring the internet trying to figure out how to do this via a macro, add-in, or separate program. I think I've been close, but no cigar. I'm hoping one of you excel pros will be able to help. Here is my situation.

I'm combining 15 phone directories that overlap in some places. I'm removing the duplicates with various find and replace methods, but I have hit a road block. The road block is with city abbreviations and city misspellings. I have over 500 cities with cities spelled and abbreviated 25+ ways.

I was able to compile a list of misspellings and abbreviations. (some by hand and some by automation from prior data)

I'm working with two spreadsheets.

(SpreadSheet1)

- my main spreadsheet with all the phone directories combined.
- the combined directory with Name, Address, City, State, Phone, etc.
- city field is littered with errors

(SpreadSheet2)

- I sorted the cities.
- Column A has the correct spelling of the city name.
- Column B thru Column (XXX) has the variations.

What I would like to do is some sort of "FIND AND REPLACE" in SpreadSheet1 using SpreadSheet2.ColB thru SpreadSheet2.Col(XXX) as the FIND And SpreadSheet2.ColA as the REPLACE.

Also, the Find and Replace has to be based on the "entire contents of the cell" otherwise some of the partial city names will be replaced.

If anyone can give me any insight on what to do I would be forever in your debt.

Thanks,

WalterP34



P.S. If you're having trouble visualizing my question here is an example

Chicago might be spelled in the city field in Spreadsheet1 the following ways.

Chi
Chcg
Chicag
Chic
Chcgo

I need to do a batch replace of all of those.

So that is why I created SpreadSheet2. like this.

Chicago, Chi, Chcg, Chicag, Chic, Chcgo

-Thanks

I've been scouring the internet trying to figure out how to do this via a macro, add-in, or separate program. I think I've been close, but no cigar. I'm hoping one of you excel pros will be able to help. Here is my situation.

I'm combining 15 phone directories that overlap in some places. I'm removing the duplicates with various find and replace methods, but I have hit a road block. The road block is with city abbreviations and city misspellings. I have over 500 cities with cities spelled and abbreviated 25+ ways.

I was able to compile a list of misspellings and abbreviations. (some by hand and some by automation from prior data)

I'm working with two spreadsheets.

(SpreadSheet1)

- my main spreadsheet with all the phone directories combined.
- the combined directory with Name, Address, City, State, Phone, etc.
- city field is littered with errors

(SpreadSheet2)

- I sorted the cities.
- Column A has the correct spelling of the city name.
- Column B thru Column (XXX) has the variations.

What I would like to do is some sort of "FIND AND REPLACE" in SpreadSheet1 using SpreadSheet2.ColB thru SpreadSheet2.Col(XXX) as the FIND And SpreadSheet2.ColA as the REPLACE.

Also, the Find and Replace has to be based on the "entire contents of the cell" otherwise some of the partial city names will be replaced.

If anyone can give me any insight on what to do I would be forever in your debt.

Thanks,

WalterP34



P.S. If you're having trouble visualizing my question here is an example

Chicago might be spelled in the city field in Spreadsheet1 the following ways.

Chi
Chcg
Chicag
Chic
Chcgo

I need to do a batch replace of all of those.

So that is why I created SpreadSheet2. like this.

Chicago, Chi, Chcg, Chicag, Chic, Chcgo

-Thanks

Is this possible??

Consider a row that has many embedded formulas in it, including a formula that reads SUM(K54,K59,K70)... with the K being the letter of the column where the formula is.

I want to know if I can use "Find & Replace" to change this to:
SUM(K56,K63,K69)..

In the Find line of the menu, I can put in ?54,?59,?70 to find all iterations..

But what do I write in the Replace line???

using ?56,?63,?69 doesn't work, Excel thinks I want to change the column letter with an actual "?" (how dumb)

Any ideas?

Thanks

Hello

I have two datasets that I need to match using vlookup. The problem is, the lookup value for one dataset is missing three 000s at the end. So one data set has numbers that look like this 114-0110-000, while the other looks like 114-0110- (you see its missing the 000s on the end, and so the Vlookup won't recognize it).

That's the basic problem. So is there a way to make excel recognize the 114-0110- and add 000 to the end of it?

Thanks
Jordan

Afternoon,

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.

Thanks for any help.

Craig

(Complete Amateur)

I just upgraded to Excel 2007, which I am enjoying a lot. The only disappointment is that you can still only use wildcards with the find part of the find and replace dialog box and not the replace part.

For example, I have 3 cities New York, Boston and Chicago. I would like in the replace part put *-Total, which would give me for example Boston-Total. Excel would know that it would put the Total at the end using the wildcard as placement. It would ignore anything with *. If I had a formula like

01-2009/My Projects/[Project List]Jan, it would be nice to put find 01-2009*Jan and replace it with 02-2009*Feb.
Is this possible?

1. How do you find and replace * in Excel?

2. How do you replace a text with an = sign in Excel?

I'm moving text from a table in Word into a table in Excel. I encountered the problem of Excel not recognizing line breaks, and removed these line breaks by finding/replacing the breaks with a vertical bar.

Text is now in the appropriate cells in the Excel table. Now, I'd like to find the vertical bars and replace them with line breaks in this Excel table, so that the text doesn't run together and I get spacing similar to how it looked in the Word table.

Any ideas? I'm at a loss.

Oh and note: I have Excel for Mac.