change old id's(values) with new id's

**gosa** · 02-17-2013, 09:39 AM

Hi,

I'm trying to change old id's with new id's in a categories column. (and if possible also change the seperator character used)
This is the data so you can visualize the problem but I also added a excel file so you can see it better or to test with.

(column A in sheet 1)
old id's
53
10
110
30

(column B in sheet 1)
new id's
2
20
120
39

(in column J in sheet 2)
categories
10###53
53
10###30###53
110
110###10
30

The numbers above should become what you see here under. (I also changed the # in a , (comma))
[edit: I should have said that it's always three # that has to change into one comma]
So 10 becomes 20 and 52 becomes 2 and so on.

categories
20,2
2
20,39,2
120
120,2
39

The numbers in categories are the old id's and they have to be replaced by the new id's (from column B) and if possible replace the # character by a , (comma).
Maybe something like vlookup or a find a replace can be used here in a macro?
Also you see that it's possible that in one cell there are 2 or more categories id like 110###10 and this should become 120 and 2 so 120,2
[edit: I should have said that it's always three # that has to change into one comma]

I've added the file in attachment with column A and B in sheet 1, and in sheet 2 you have column J with the categories and sheet 3 has what it has to become.

ps: in column A I just used 4 numbers but there are over a hundred in reality

Thanks so much in advance.

Example_rs_hs_categories.xlsx

**Andrew-R** · 02-17-2013, 10:11 AM

I had to add a little user-defined function to your workbook to do some of this:

Please Login or Register  to view this content.

But with that in a module I could use the formula:

=LookupReplace(SUBSTITUTE(TRIM(SUBSTITUTE(Blad2!J2,"#"," "))," ",","),Blad1!$A$2:$B$5,2)

In cell K2 of the results sheet, and just drag it down.

**gosa** · 02-17-2013, 10:48 AM

Great!!!! I can't believe you made something so quick!!!

I just tried it and it seems to work. I'm really happy :-)

Since I'm using a dutch version of Excel I had to change the function names in dutch and replace the comma's with semicomma's.
So for other dutch ppl around this the formula in dutch (all credit still goes to Andrew!!!!):

Please Login or Register  to view this content.

As you can see I also changed the part where you substitute # into a space.
I forgot to say that it was ### that had to be replaced into a comma and not one #. My mistake but was easily fixed by changing it into SUBSTITUEREN(Blad2!J2;"###";",")

Thank you Andrew! You made my day!!!!!!!!!!

Do you think it would be difficult to make it do it automatically with a button so no dragging is needed?
The reason I ask this, is because the sheet has around 4100 rows if I'm not mistaken.

I'm still happy that this works.

**Andrew-R** · 02-17-2013, 11:17 AM

I converted the #'s to spaces because then the TRIM function would convert multiple consecutive spaces into one space - I wasn't sure you'd always have 3 hashes as your delimiter.

So using SUBSTITUTE would convert:

Please Login or Register  to view this content.

Into

Please Login or Register  to view this content.

And then trim would make that:

Please Login or Register  to view this content.

And then the 2nd substitute would replace spaces with commas to make:

Please Login or Register  to view this content.

As for dragging the formula down rows - Excel has an inbuilt short-cut for this. If you put the formula at the top of the column you want it in then when the cell is selected there'll be a small black square at the bottom right-hand corner. If you double-click this square Excel will copy the formula down as far as there is data in the adjacent column.

**gosa** · 02-17-2013, 11:43 AM

Andrew you are a star!!!
I didn't know about the double clicking!!! This is fantastic, especially when you have hundreds or thousands of rows.

Thanks for the explanation as well !!!

I will mark this as solved.

You sir, have a great day!!!!!