Replacing text according to a table?

How many words are we talking about... ? Where are the strings in which the words exist located ?

You can embed a certain number of SUBSTITUTE functions but it's likely a VBA based routine would be preferable

A sample file would be useful here I think.

There are 25 diffrent numbers.
the documents I wanna change each have one big column filled with multiple occurances of these numbers.

You can embed a certain number of SUBSTITUTE functions but it's likely a VBA based routine would be preferable

Ok, so there is no easy built in functionality in Excel that could replace a bunch at the same time?

(I was hoping for something similar to a validation list.
That you could have a range of 2 columns. one with the words to be replaced and the other with their corresponding replacers.
and then use those to automatically replace those words)

Just wanted to check if there was any easy built in functionality in Excel that could swap a bunch of words at the same time.

well you can use Edit -> Replace feature but without seeing an example reflective of your data it's hard to comment in terms of the specifics you may need to apply.

To reiterate earlier point, it would be a very good idea to post a small sample file . It will make life easier in the long run... ie it will indicate as to whether or not the numbers you wish to replace are self-contained within a given cell or whether in fact they form part of a string within a cell ... where are the 25 numbers & associated words to be stored etc etc... a file can speak a thousand (+) words...

Ok, here's an example file with a column of numbers (hexadecimal), and how I would want to change them.

well you can use Edit -> Replace feature

would be too tedious. I've got alot of files that I want to replace stuff in.

So I know I sound like a bore/simpleton but it would good to specify expected output for at least some of those values... ie should 1C become "DiskC", should "04" (stored as a text string in ID column rather than as a number) become "card" or "0card" ?

Oh, my bad. should've included a fiew more examples to make it clearer.

1C is just a hexadecimal number. It's not a variant of 1.
It's a separate ID and should have it's own replacement word.

Each unique ID is replaced by a unique word. No prefixes or fancy things. Just a standard 1:1 replacement.

(i haven't included all the ID:s and replacement words in the file, just a fiew)

A formula based approach could be:

C4: =LOOKUP(REPT("Z",255),CHOOSE({1,2},$B4&"",VLOOKUP($B4,$G$7:$H$10,0)))
copied down
(note: the above is in UK regional format -- you will most likely need to revise delimiter according to your own regional settings and/or translate the Functions to Swedish equiv - see link in sig for Fn Translations)

Results copied over B and C removed... else VBA.

If you want VBA let us know.

Yes, think I'll go for VBA.

I'm very new to it. I know how to create the macro and where to put it, but nothing about how to code it.

Would appreciate a code snippet to get me going.


...Guessing it would be something like this.

Sub ReplaceID()
If Selection.Value = "1" Then
Selection.Value = "disk"
End Sub

Based on your sample file perhaps something along the lines of:

Please Login or Register  to view this content.

In truth it might be a tad better to dispense with the Array and just use the Range, does this make more sense ?

Please Login or Register  to view this content.

Essentially iterating the values to replace as dictated by the values in the Range in G and replacing them in the B range with the value adjacent to the criteria (Range in H).

The End(xlUp) is used in essence to ascertain the last used cell in the range of interest (ie goes to the bottom of Column G and then Upwards until it finds "something") - so as you add more criteria and re-run the ranges update without you being required to adjust your code to encapsulate the new data.

Thanks, that made it much clearer.

another question: How do I specify worksheets?

I've copied the B column from Sheet1 to Sheet 2.
So in the macro I've tried adding 'Worksheets("SheetX").' as a prefix to parts that adress columns.
But it desn't work.

Seems to work OK on the 3rd line (the one starting with "Set rngReplace")
however, it gets stuck on the 5th (the one starting with "With")

What am I doing wrong?

Public MassReplace()
Dim rngReplace As Range, rngCell As Range
Set rngReplace = Worksheets("Sheet1").Range(Cells(7, "G"), Worksheets("Sheet1").Cells(Rows.Count, "G").End(xlUp))
For Each rngCell In rngReplace.Cells
With Worksheets("Sheet2").Range(Cells(4, "B"), Worksheets("Sheet2").Cells(Rows.Count, "B").End(xlUp)).SpecialCells(xlCellTypeConstants)
.Replace rngCell.Value, rngCell.Offset(, 1).Value, LookAt:=xlWhole
End With
Next rngCell
End Sub

(the editing from the original is marked with bold)

See if the below helps...

Please Login or Register  to view this content.