Hi all,
Does anyone have a script I can run that will find/replace with values provided in a column?
Column D has values separated by commas, I specified in column J the values and in column K what they should be replaced with. Ideally a click and drag is what I am looking for.
This will do it. Not a click and drag though.
Please Login or Register to view this content.
Thanks,
Mike
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved.
Mike a bit of a noobie... What column is this going in, and uh more or less what am I doing with it lol.. Making it a formula with an equal sign?Originally Posted by mike7952
You asked for a script so I assumed you was familiar with macros. Attached is your example work. To run the code press Ctrl + m at the same time. You can press Alt + F11 to view code. Also can press Alt + F8 to bring up a Macro Dialog box where you can see the Macros
Last edited by mike7952; 11-28-2016 at 11:55 PM.
Mike, just wanted to take a sec and thank you for everything thus far. I got it to work perfectly in the example sheet I posted
I am trying to apply it to a real example now and see to be stuck. Can you take a look at this one?
The values I am trying to replace are in J & K again.
Give this a try
Please Login or Register to view this content.
Tried that but it doesn't seem to run? Pressed control + M and all.
My bad change this line
FROM
TOPlease Login or Register to view this content.
Please Login or Register to view this content.
Edit Please ignore. See post #11.
If you are interested in a formula solution array enter this (Ctrl + Shift + Enter) in C1 and fill down.
=IFERROR(INDEX($K$1:$K$26,INDEX(MATCH(TRIM(MID(SUBSTITUTE($D1,",",REPT(" ",99)),(COLUMN($A:$B)-1)*99+1,99)),$J$1:$J$26,0),1))&","&INDEX($K$1:$K$26,INDEX(MATCH(TRIM(MID(SUBSTITUTE($D1,",",REPT(" ",99)),(COLUMN($A:$B)-1)*99+1,99)),$J$1:$J$26,0),2)),INDEX($K$1:$K$26,MATCH($D1,$J$1:$J$26,0)))
Last edited by FlameRetired; 11-29-2016 at 09:33 PM.
Dave
Edit Please ignore. See next post.
Or if you wish to re-use the User Defined Function Concatall from previous posts array enter this.
=IFERROR(Concatall(INDEX($L$1:$L$26,INDEX(MATCH(TRIM(MID(SUBSTITUTE($E1,",",REPT(" ",99)),(COLUMN($A:$B)-1)*99+1,99)),$K$1:$K$26,0),N(IF(1,{1,2})))),","),INDEX($L$1:$L$26,MATCH($E1,$K$1:$K$26,0)))
Last edited by FlameRetired; 11-29-2016 at 09:32 PM.
This instead. Still uses Concatall and is array entered. I had overlooked that there are sometimes more than two codes in column D cells.
=Concatall(INDEX($K$1:$K$26,INDEX(MATCH(TRIM(MID(SUBSTITUTE($D1,",",REPT(" ",99)),(ROW(INDIRECT("1:"&LEN($D1)-LEN(SUBSTITUTE($D1,",",""))+1))-1)*99+1,99)),$J$1:$J$26,0),N(IF(1,ROW(INDIRECT("1:"&LEN($D1)-LEN(SUBSTITUTE($D1,",",""))+1)))))),",")
UDF ...
Use:Please Login or Register to view this content.
=FindAndReplace(D1,$J$1:$K$26)
Last edited by Phuocam; 11-29-2016 at 11:03 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks