Removing double quotes around strings returned by formulae

Question

I've constructed a cell range using concatenate which works fine except that when it is returned, Excel sticks double quotes around them and my match function fails.

I've tried trim&substitute. I've tried putting the function into a cell and removing the function to make it the actual value. nothing works.

Any suggestions please?

e.g. Concatenate("D","35",":","D","42") is displayed as D35:D42 in excel but returns "D35:D42" as the parameter of Match.

mathetes · Answer

its_the_wil&nbsp;&nbsp;hi , i have the same problem, i couldn't solve it with indirect !!&nbsp;any suggestions plz ?&nbsp;It's probably not the same problem then, wouldn't you think? So let's start with a more complete description of whatever the problem is that you're experiencing. And, if it's possible--if your spreadsheet doesn't contain confidential or proprietary info--please post a copy of the spreadsheet itself in OneDrive or GoogleDrive and paste a link here that grants edit access.

mtarler · Answer

In addition to mathetes comments above I would like to add that this should be a NEW post. The original post was solved.As for your issue we need to see what you tried. I'm not sure what HLOOKUP returned "StaffTBL[Dec-2022]" I think you want your HLOOKUP to return the column header name and then put that inside the [ ] but that might be easier using INDEX( MATCH ()) or if you have Excel 365 then just use XLOOKUP. But maybe try something like:=COUNTIFS(StaffTBL[Position],"Head Teacher",StaffTBL[Gender],"MALE",StaffTBL[ INDEX(StaffTBL[#Headers], MATCH( ... ) ) ],"Active")

mathetes · Answer

kimbo1160&nbsp;&nbsp;Not sure about this, but it looks to me as if what might want is =INDIRECT("D35:D42")&nbsp;Try that. If it's not what you're seeking, come back with an example spreadsheet and a more complete description.

kimbo1160 · Answer

thanks Mathetes. Looks like that's worked. you're a life saver 🙂

its_the_wil · Answer

kimbo1160&nbsp;hi , i have the same problem, i couldn't solve it with indirect !!&nbsp;any suggestions plz ?

Forum Discussion

Removing double quotes around strings returned by formulae

7 Replies

Resources