Q:

Advanced substitute in excel with range

I'm trying to replace 1400 cells with text into cells with ids, I've attached a picture so you can understand it easier. I'm trying to do this only by using cells because I have 1400 cells I need to replace.
I've trying using substitute using a range but I can't seem to make it work.
=SUBSTITUTE(H2;K2:K999;J2:J999)

A:

It sounds like you don't want to use a VBA solution? 
If you don't mind moving some stuff around, try this. 

Damn, that image isn't big enough, is it? You can see the layout though. "Original Text" is in C1, and the word ID is in A5, and I've numbered D1:I1 1-6. Now paste this formula into D2: =SUBSTITUTE(C2,OFFSET($B$6,D$1-1,0),OFFSET($A$6,D$1-1,0))
And drag it right and drag all the columns down. Your desired output should be in column I. It's not the most computationally efficient, but it's super easy to implement and works with any size lookup table (just drag out more columns and number them) and any number of items in the text fields.

