Joining Text with Hypen

[colarguns]

I have asked this before in this forum (https://www.mrexcel.com/forum/excel-questions/1062061-separating-characters-hyphen.html#post5100161), but have ran into a hiccup.

Cell AJ2 has "Khaki" without quotes
Cell AJ3 has "Medium Gray" without quotes

In cell K2 I have (=SUBSTITUTE(AJ2&"/"&AK2, ""," ") which results in "Khaki/ Medium Gray" without quotes. Notice the space after hyphen

In cell L2 I have (=SUBSTITUTE(TRIM(SUBSTITUTE(K2,"/"," "))," ","-") which results in "Khaki-Medium-Gray" without quotes

The objective is (Khaki/Medium Gray) without parentheses. I am trying to get rid of that space. I have ensured all cells containing text do not have any other characters, and all text have been formatted to left alignment, center, right, all with no differences to results. The cells are also formatted as general.

Please note, what I have are two columns that contain colors. Not all colors will be the same. Example, I might have (Bright White) in one cell, and (Bright Purple) in another cell. All with the same objective of (Bright White/Bright Purple).

I am mentally challenged tonight, lol, so any help would be appreciated. Thank you

 

[nardagus]

I'm a bit confused.
You say that cells AJ2 and AJ3 contain values while in K2 you use AJ2 and AK2. What value AK2 contains then? Another thing is that in K2 you are trying to find nothing and replace it with space. That won't work.

 

[colarguns]

AI2 has a word in it
AJ2 has a word in it
In cell K2, I am trying to join these two words with /

Example: AI2 has "White" without quotes
Example: AJ2 has "Royal Red"without quotes
In cell K2, I wish to see (White/Royal Red) without the parentheses

The problem I have ran into, is that when I join these with the formulas I mentioned above, I end up with a space after the "/".
The objective is (White/Royal Red)


Also, as I am working on this, I am seeing where I might just have one word, instead of two. Which means I would have just "White" in cell K2, without quotes.
I can filter those one word cells I guess, but it would be simpler if I had the right formula to say (if it's only one word, then it's only one word). I have
roughly 82,000 rows to work on. Thanks

PS....As I recently did another (Text to Column), the cells changed. The cells in this post are correct.

As an FYI, I have one column (AI) that has from 1 to 16 colors. They are all separated with a comma. So I conduct a (Text to Column) to
separate these colors. I only want the first two colors, and they are to be separated by "/" without quotes, and no spaces before or after the "/".
Some cells in column AI through AX will only have one color, so I need to see only "White" as example, without quotes and without the "/".
Hope that made sense. Thanks

 

[nardagus]

Hmmm. If it is only a matter of concatenating two cells with a separator then all you need in K2 is:

Excel 2016 (Windows) 32 bit

	AI	AJ	AK
2	White	Royal Red	White/Royal Red

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Arkusz1​

Worksheet Formulas Cell Formula AK2 =AI2&"/"&AJ2 <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[colarguns]

Tried that. Result is White/ Royal Red
Notice the space between / and Royal? That is the issue I am having.

 

[sandy666]

maybe: =TRIM(AI2)&"/"&TRIM(AJ2)

 

[colarguns]

That worked Sandy! Thank you! BUT....what if I end up with one word? Is there a way to tell Excel, don't try putting a / after the first word, if there is not a second word?

 

[sandy666]

=if(aj2="",trim(ai2),trim(ai2)&"/"&trim(aj2))

 

[colarguns]

You are hereby awarded the MS Excel Pulitzer Award of the Century! That worked great!!!!! Thank you so much Sandy!

 

[sandy666]

Extended version: =IF(AJ2="",SUBSTITUTE(TRIM(CLEAN(AI2)),CHAR(160),""),SUBSTITUTE(TRIM(CLEAN(AI2)),CHAR(160),"")&"/"&SUBSTITUTE(TRIM(CLEAN(AJ2)),CHAR(160),""))