using substitute formula with a vlookup table

[ronmaltase]

Hello, I’m trying to use the SUBSTITUTE formula to replace one value from a table with another value from that same table. I thought I could use a VLOOKUP table with the substitute formula. Here’s the formula I’m using. Column 1 on my mainlist table is the new text, column 3 on my mainlist table is the old text. Any ideas?

=SUBSTITUTE(O2,VLOOKUP('mainlist'!$A$2:$C$61, 'mainlist'!$A$1:$C$61,3,FALSE),VLOOKUP('mainlist'!$A$2:$C$61, 'mainlist'!$A$1:$C$61,1,FALSE))

-Thanks, Ron M.

 

[Eloise T]

Ron,
You are mixing text with numerical data. The SUBSTITUTE function is expecting text and you are giving it numerical data via the VLOOKUP function. You are probably getting a #VALUE! error?

 

[ronmaltase]

yes. darn. I tried converting my table data to text. some of my table values are numbers (i.e. 96360) and some are text (i.e. C7600). I assumed all of my original document values are text, because every cell has a combination of text and number. but some of the original values look like this 96360X1, and some look like 96360. so maybe the original document is treating some of them as numbers too?

 

[ronmaltase]

I noticed when I type in one of the actual values in my formula, for the 'old text', like 96360, the formula works. it goes to the vlookup table and returns the 'new text' 3800121. like this: =SUBSTITUTE(O2,96360,VLOOKUP('mainlist'!$A$2:$C$61, 'mainlist'!$A$1:$C$61,1,FALSE)). but there's so may 'old text' values, that's why I wanted to use the table.

 

[ronmaltase]

I'm stuck. :-( any ideas anyone?

 

[ronmaltase]

=SUBSTITUTE(O2,96360,3800009) -this is what I'm looking for except I'd like to pull the "old text" and the "new text" from a table? This substitute formula works but I don't want to have to enter the old and new values in the formula each time. :-( each cell string has several of the 5 digit 'old text' values that need to be replaced with the 7 digit 'new text'.

 

[NARAYANK991]

Hi Ron ,

If you could upload a sample workbook , with all of the variations in the data well represented , it would be a matter of minutes to arrive at a satisfactory solution.

In the absence of data , all that others can offer will be suggestions , which may or may not hit the mark right off.

Narayan

 

[Eloise T]

When you say "table" did you mean array....$A$2:$C$61?
If your table is limited to $A$2:$C$61, what's preventing you from simply formatting 'mainlist'!$A$2:$C$61 as text?

 

[ronmaltase]

I've attached a sample workbook...

It seems like I've done some pretty complex stuff (complex for me) through the years, but sometimes I just get stumped. I've learned so much from Chandoo.org and all of you over the years. I'm sure I'm doing something wrong.

 

[Khalid NGO]

Hi Ron,

If I understood you correctly, can use this {array formula} :

=SUBSTITUTE(A2,IFERROR(--MID(A2,FIND(" ",A2)+1,5),--LEFT(A2,5)),VLOOKUP(IFERROR(--MID(A2,FIND(" ",A2)+1,5),--LEFT(A2,5)),--'cdm table'!$A$2:$B$35,2,FALSE))

Enter with Ctrl+Shift+Enter

Wrap it with IFERROR(.....,"") if required.

Regards,

 

[NARAYANK991]

Hi Ron ,

If the existing sentence in column A is :

Delete 96360 , 96375 X 2 then add 96365 , 96366 and 96361 X 1 (1 already charged)

this would change to :

Delete 3800009 , 3800015 X 2 then add 3800013 , 3800012 and 3800010 X 1 (1 already charged)

Given that there are two variables :

1. The number of such replacements varies

2. The number of digits in each replacement also vary

I doubt that a formula can cater to both the above requirements.

Narayan

 

[ronmaltase]

Thank you so much Khalid NGO and NARAYANK991 - I did try the formula suggested by Khalid. It only replaced the first instance of the 5 digit "old text". Is there a way I can change the formula to look at the second instance, then the third, etc. That way I could apply it, then copy the result and paste values, then change the formula to substitute the 'second' instance, and so on.

 

[Khalid NGO]

Thank you so much Khalid NGO and NARAYANK991 - I did try the formula suggested by Khalid. It only replaced the first instance of the 5 digit "old text". Is there a way I can change the formula to look at the second instance, then the third, etc. That way I could apply it, then copy the result and paste values, then change the formula to substitute the 'second' instance, and so on.

Hi Ron,
Good day and Happy New Year...

Sorry, I thought you want to replace just first instance, my bad.

This may required VBA as pointed out by Narayan Sir. [I am zero in vba]

Regards,

 

[NARAYANK991]

Hi Ron ,

See if this is OK. You need to run the macro within the file.

Narayan