Concatenate based on filtered results in first column

[jolyman17]

I have a project I'm working on that requires me to make a selection in column A (Choosing a template that contains the words Right, Left or Front). The selection from column A is used to generate a filename in column C but I don't want to use the word template in the filename. i only want the filename to pull the position (Left, Right or Front) from column A so the filename works out to be: Effect_Front_NY (Effect_ would be text added during the concatenate, Front would be pulled from Template_Front in column A and NY would be pulled from column B.)

Screen Shot 2016-08-08 at 11.11.49 AM.png


I'm sure there is a way to do this but I'm just overlooking it. Appreciate any help that you can offer!

[AliGW]

In C1, copied down (not tested):

="Effect_"&SUBSTITUTE(A1,"Template_","")&"_"&B1

[mikeTRON]

In cell C1:

=SUBSTITUTE(A1&"_"&B1,"Template","Effect")

http://chandoo.org/excel-formulas/substitute.shtml

[José Augusto]

Try this formula

=IF(COUNTIF(A1,"*Front*")+COUNTIF(A1,"*Left*")+COUNTIF(A1,"*Right*")>0,SUBSTITUTE(A1&B1,"Template","Effect"),"")

[jolyman17]

Thanks for the help. I realized that I have one more variable I need to consider. What if the values in column A are more varied? The text in my first example was the same except for the position. Now I have text that has different character lengths so the substitute function doesn't seem to work. I also need to include additional text at the end of the filename.

In the sample below the filename NBA_GAME_MU_ATL_LEFT_F is generated this way:
NBA_GAME_MU_ - Text that will be at the start of every filename
ATL - Tricode (Column A)
_- Underscore that should alway separate the tricode from the position that follows
LEFT - Pulled from the Comp in Column B. I need to be able to pull FrontBack, Right and Left out of the values in Column
_F - Text that should come at the end of every filename

Screen Shot 2016-08-08 at 12.37.13 PM.png

[AliGW]

As this is now getting more complicated, please attach a sample workbook. Make sure there is just enough data to demonstrate your need. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are shown, mock them up manually if necessary.

Remember to desensitize the data.

Click on GO ADVANCED and then scroll down to Manage Attachments to open the upload window.

[jolyman17]

Sample file uploaded with before and after tabs.

[mikeTRON]

Your sample doesn't make any sense.
Row 2 shows ANIM_MATCH_FrontBack_01_NBA_TEMPLATE changing to NBA_GAME_MU_ATL_LEFT_F... shouldnt that be FRONT_F and not LEFT_F

Having a precise and accurate sample will allow us to help you

[jolyman17]

You're right - I missed that. My apologies. I updated the file.

[José Augusto]

Hi
I use a helper column M to simplify the formula
So in M2 use

=1*(COUNTIF(B2,"*Front*")>0)+2*(COUNTIF(B2,"*Left*")>0)+3*(COUNTIF(B2,"*Right*")>0)

In K2 use

=IF(M2>0,"NBA_GAME_MU_"&A2&"_"&INDEX({"FRONT_L";"LEFT_L";"RIGHT_L"},M2),"")

see the file