CONCAT only if cell does NOT contain specific text

lichldo

New Member
Joined
Apr 19, 2022
Messages
44
Office Version
  1. 365
Platform
  1. MacOS
I have the following CONCAT formula that works great - '=CONCAT(H5," ",I5," ",J5," ",K5," ",L5," $",M5)

However, I need it to become an IF forumula so that if H5 does NOT contain a "-" than this forumula will just give me the H5 value (as if the formula for this cell is just '=H5
but if the cell DOES contain a "-" than it does my above CONCAT forumula

is there a way to accomplish that?
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
75,871
Office Version
  1. 365
Platform
  1. Windows
How about
Excel Formula:
=IF(H5="-",TEXTJOIN(" ",,H5:L5)&" $"&M5,H5)
 

lichldo

New Member
Joined
Apr 19, 2022
Messages
44
Office Version
  1. 365
Platform
  1. MacOS
How about
Excel Formula:
=IF(H5="-",TEXTJOIN(" ",,H5:L5)&" $"&M5,H5)
That only returns the value in H5 whether there is a "-" or not when I apply to my sheet - not any values in I:M
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
75,871
Office Version
  1. 365
Platform
  1. Windows
What sort of values do you have in H5?
 

lichldo

New Member
Joined
Apr 19, 2022
Messages
44
Office Version
  1. 365
Platform
  1. MacOS
What sort of values do you have in H5?
I have names of project phases and roles in column H. So the phases would be something like "Creative Design" and roles would be something like " - Creative Senior -Art Direction"

So basically for the phases that do not have a "-" I just want to duplicate that value in the cell I am putting this IF/CONCAT forumula in to act as a header.
And for the roles that do have a "-" I want to CONCAT all of the data from H5:M5, adding in a $ before the value in M5
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
75,871
Office Version
  1. 365
Platform
  1. Windows
Ok, how about
Excel Formula:
=IF(ISNUMBER(FIND("-",H5)),TEXTJOIN(" ",,H5:L5)&" $"&M5,H5)
 

lichldo

New Member
Joined
Apr 19, 2022
Messages
44
Office Version
  1. 365
Platform
  1. MacOS
Ok, how about
Excel Formula:
=IF(ISNUMBER(FIND("-",H5)),TEXTJOIN(" ",,H5:L5)&" $"&M5,H5)
That works perfectly!!

Now that I can see if function, I wonder if it's possible to add 1 more condition?
can it add - If H5 contains "-" but I5 is blank, than do nothing/leave blank
but still if H5 contains "-" and I5 has any value, than CONCAT
and still if H5 does not contain "-" than just duplicate the value in H5
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
75,871
Office Version
  1. 365
Platform
  1. Windows
Could I5 be blank & H5 not have a -
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
75,871
Office Version
  1. 365
Platform
  1. Windows
Ok, how about
Excel Formula:
=IF(ISERROR(FIND("-",H5)),H5,IF(I5="","",TEXTJOIN(" ",,H5:L5)&" $"&M5))
 
Solution

Forum statistics

Threads
1,170,961
Messages
5,872,999
Members
432,955
Latest member
minhnhat2504

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top