how to concatenate if text starts with qvs* only for multiple columns

[lunaryi]

Hello,
I’m stuck in a situation where I need to be able to use VLookup to pull information based on text strings that all start with qvs. I’ve used normal concatenate across the 5 columns in question but it returns too much information and my vlookup formula errors. Is there a way to write a concatenate formula that will only pull texts from each of those five columns only if the text in that cell starts qvs* ?

 

[AtlantaAccessAndExcel]

Use the IF function (IF( condition, value_if_true, [value_if_false] ))

Use the MID or LEFT function to grab the first 3 characters MID( text, start_position, [number_of_characters] )

Combine these with CONCAT and you will get a messy formula, but it should work...

 

[lunaryi]

Thank you for your suggestion. I input =concatenate(if(left(AA3,3)=qvs),”AA3”,””). Excel came back with a name error: is possible can you point to where it went wrong?

 

[jtakw]

Hi,

Put quote marks around "qvs", remove quote marks for AA3

BTW, don't know why you're using CONCATENATE, you have a Single value...

 

[lunaryi]

Thanks jtakw, I was using concatenate because I’ll have multiple columns to include but I wanted to see if the base formula returned the value I expected for one column first.

I am still receiving the name error, full formula is listed below:

=Concatente(IF(LEFT([@[PFS_Problem No3]],3)="qvs",[@[PFS_Problem No3]],""),IF(LEFT([@[PFS_Problem No5]],3)="qvs",[@[PFS_Problem No5]],""))

tha k you for your help

 

[DanteAmor]

You are missing an "A" in concatenate

=Concatenate(IF(LEFT([@[PFS_Problem No3]],3)="qvs",[@[PFS_Problem No3]],""),IF(LEFT([@[PFS_Problem No5]],3)="qvs",[@[PFS_Problem No5]],""))

 

[DanteAmor]

It may be so

=IF(LEFT([@[PFS_Problem No3]],3)="qvs",[@[PFS_Problem No3]],"") & IF(LEFT([@[PFS_Problem No4]],3)="qvs",[@[PFS_Problem No4]],"") & IF(LEFT([@[PFS_Problem No5]],3)="qvs",[@[PFS_Problem No5]],"") & IF(LEFT([@[PFS_Problem No6]],3)="qvs",[@[PFS_Problem No6]],"") & IF(LEFT([@[PFS_Problem No7]],3)="qvs",[@[PFS_Problem No7]],"")