CONCAT only if cell does NOT contain specific text

lichldo

Board Regular
Joined
Apr 19, 2022
Messages
65
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

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
How about
Excel Formula:
=IF(H5="-",TEXTJOIN(" ",,H5:L5)&" $"&M5,H5)
 
Upvote 0
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
 
Upvote 0
What sort of values do you have in H5?
 
Upvote 0
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
 
Upvote 0
Ok, how about
Excel Formula:
=IF(ISNUMBER(FIND("-",H5)),TEXTJOIN(" ",,H5:L5)&" $"&M5,H5)
 
Upvote 0
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
 
Upvote 0
Could I5 be blank & H5 not have a -
 
Upvote 0
Ok, how about
Excel Formula:
=IF(ISERROR(FIND("-",H5)),H5,IF(I5="","",TEXTJOIN(" ",,H5:L5)&" $"&M5))
 
Upvote 0
Solution

Forum statistics

Threads
1,213,507
Messages
6,114,029
Members
448,543
Latest member
MartinLarkin

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
Back
Top