find and replace

[darryncooke]

I have a table of about 150K records that I am trying to clean up.

In 1 column there are types of businesses but they are not uniform. For example it could say, accountant, accountants, accounting, acct, acct/banking/tax, etc..

How can I find an replace with say 'Accounting Services'. Lets say I want to find all cells that contain acct and replace the whole cell content with 'Accounting Services'?

thank you,

Darryn Cooke

http://www.darryncooke.com

| The New Orange County Graphic designer and Marketing and Advertising Consultant

http://www.bloobie.com

| Marketing and Advertising blog

 

[Andrzejek]

If you say "table" I assume it is an Access data base.

First, make sure you get ALL records that you want to change, no more, no less:

SELECT DISTINCT TypesCol
FROM MyTable[blue]
WHERE TypesCol LIKE '%acco%'
OR TypesCol LIKE '%acct/%'[/blue]

Then you can simply:

UPDATE MyTable
SET TypesCol = 'Accounting Services'[blue]
WHERE TypesCol LIKE '%acco%'
OR TypesCol LIKE '%acct/%'[/blue]

Have fun.

---- Andy

 

[darryncooke]

sorry i should have specified - excel worksheet

i said table because once formatted it will go into a mysql dbase.

Darryn Cooke

http://www.darryncooke.com

| The New Orange County Graphic designer and Marketing and Advertising Consultant

http://www.bloobie.com

| Marketing and Advertising blog

 

[Madawc]

If you select a column you can then do find-and-replace for just that column. Does that help?

Madawc Williams (East Anglia, UK). Using Crystal 10 & 11.5 with Windows XP

 

[kjv1611]

darryncooke,

Are you looking to do this in VBA code, or are you looking for a basic find/replace option?

I'm afraid that simple/basic find/replace will not do what you want to do.

You could do something like this in VBA pretty easily, though.

Here's just a sample, but I can't guarantee it's 100% correct, as I'm not testing - just typing:

Private Sub FixAccounting()
  Dim wb as Workbook
  Dim ws as Worksheet
  Dim x as Long [green]'Rows[/green]
  Dim y as Long [green]'Columns[/green]

  Set wb = ActiveWorkbook
  Set ws = wb.ActiveSheet

  y = 1 [green]'If you need for more than one column, you'd need put this in a loop outside the x loop.[/green]

  For x = 2 to 150,001 [green]'Assuming you have a header row, this starts in row 2, and goes for 150,000 records.[/green]
    With ws
      If InStr(.Cells(x,y),"acct") Then
        .Cells(x,y).Activate
        .Cells(x,y).Formula = "Accounting Services"
      End If
    End With
  Next x

  x = 0
  y = 0
  Set ws = Nothing
  Set wb = Nothing

End Sub

Another option would be using a formula 1 column over, and then copy the values from the formula column after finished, and pasting to another if necessary with paste special - values.

The setup could be this:

Column B (numeric 2) is your column which has your values you want to fix.

Column C can have the formula. The formula piece you'd need for searching for acct in the cell is [blue]Find[/blue]. Of course, you'll need conditions, etc...
If(Find(B2,"acct")=True,"Accounting Services",B2)
Something like that, I can't promise that'll work, as I just quickly typed it out here..

So, all that said, do you have a specific preference as to how to get the job done, or what? If this all doesn't work, be sure to give more details about your situation.

--

"If to err is human, then I must be some kind of human!" -Me

 

[darryncooke]

i will try the formula version and I dont know anything about visual basics.

but i did know that he simple find/replace just wouldnt hack it.

Darryn Cooke

http://www.darryncooke.com

| The New Orange County Graphic designer and Marketing and Advertising Consultant

http://www.bloobie.com

| Marketing and Advertising blog

 

[kjv1611]

Give it a shot, and post back and let us know how it worked out. If you run into any problems/questions, post back with as much detail as you can, so we can help you find a solution.

--

"If to err is human, then I must be some kind of human!" -Me

 

[darryncooke]

the formula dindt work - it only checked if the cell value was that not if it contained that string - also it didnt change the value

Thanks but I think ill just manually make most of these changes. Sweat equity at its finest.

Darryn Cooke

http://www.darryncooke.com

| The New Orange County Graphic designer and Marketing and Advertising Consultant

http://www.bloobie.com

| Marketing and Advertising blog

 

[AnotherHiggins]

Whatever route you take, I think the most difficult part will be coming up with a list of all the things you need to look for to replace with something else.

Based on what you've said so far, I'd suggest just using a VLookup.

If you have 150K records in Excel, you must be in 2007....

[li]Copy the column containing the 'types of businesses'[/li]
[li]Paste in Column A of a new sheet[/li]
[li]In that new sheet, go to Data > Data Tools > Remove Duplicates[/li]

You now have a list of unique values from that column

There's no real easy way around this next part - you need to go through the list and decide what you want each value to be replaced with. Place the replacements in column B.

Now back in the original table:

[li]insert a column beside 'types of businesses'[/li]
[li]Use a formula like
[tab][tab][COLOR=blue white]=Vlookup(A2, LookupTable, 2, 0)[/color]
(where A is the column containing 'types of businesses' and LookupTable is the new table with replacements that you just made)[/li]

[li]Copy the column containing formulas[/li]
[li]Paste Special > Values to replace the formulas with values[/li]
[li]Delete the column that originally contained the 'types of businesses'[/li]
[li]Rename the new column so that it takes the place of the column you just deleted[/li]

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[darryncooke]

yup i did something along those lines except I used find and replace.

I use excel 2003 and had 5 worksheets that I just slected the columns and dind big batches of find and replace.

I am officially sick of find and replace.

Darryn Cooke

http://www.darryncooke.com

| The New Orange County Graphic designer and Marketing and Advertising Consultant

http://www.bloobie.com

| Marketing and Advertising blog