Substitute function with VLOOKUP

**gach** · 05-23-2013, 04:05 PM

Originally Posted by JBeaucaire

Like so:

=SUBSTITUTE(SUBSTITUTE(D$1," - Long",""), " - Short", "")

...or

=SUBSTITUTE(SUBSTITUTE(D$1," - Long","Consumer"), " - Short", "Consumer")

Is it possible to combine the second option with vlookup in order to find matches inside the text and replace those with the correspondent value?

**JBeaucaire** · 05-23-2013, 04:57 PM

Possibly. Need more information to suggest anything.

==============
When you joined the forum recently you agreed to abide by the Forum Rules, but in haste I fear you might not have actually read them. Please stop and take a moment to read them now. We all follow these rules for the benefit of all, as must you. Thanks.

(link above in the menu bar)
===================================

**gach** · 05-23-2013, 05:34 PM

What I need to do is this:

I have a column (B) with this function =SUBSTITUTE(A1,",","") in all cells to replace commas inside the text in column (B).

Inside that function I would like to add other function that looks inside a range of cells for a value and replace that exact match by the text inside the correspondent one.

This is the cells range where I have the values stored =VLOOKUP(B1,'Sheet 2'!C$2:D$33,2,0)

p.s. - My excell is in Portuguese so it might be some errors on the functions (which where corrected by me).

**JBeaucaire** · 05-23-2013, 07:30 PM

So perhaps you replace the A1 in the first formula with the entire second formula?

Or replace the B1 in the second formula with the entire first formula?

**gach** · 05-24-2013, 05:42 AM

That's not what I need. What I need is to combine both because I still need to remove all the comas...

I need something like this =SUBSTITUTE(A1;",";"";)=VLOOKUP(A1;'Sheet 2'!C$2:D$33;2;0)

**JBeaucaire** · 05-24-2013, 10:59 AM

Please provide a real-world example, talking in formulas only works when it's 100% clear what the problem/need is. Best is to upload a sample workbook showing us sample data on Sheet1 and Sheet2 and a manual mockup of the results you need.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic