Joining Text with Hypen

colarguns

New Member
Joined
Jul 2, 2016
Messages
25
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
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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.
 
Upvote 0
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
 
Last edited:
Upvote 0
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
AIAJAK
WhiteRoyal RedWhite/Royal Red

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]

</tbody>
Arkusz1

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]AK2[/TH]
[TD="align: left"]=AI2&"/"&AJ2[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Tried that. Result is White/ Royal Red
Notice the space between / and Royal? That is the issue I am having.
 
Upvote 0
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?
 
Upvote 0
You are hereby awarded the MS Excel Pulitzer Award of the Century! That worked great!!!!! Thank you so much Sandy!
 
Upvote 0
Extended version: =IF(AJ2="",SUBSTITUTE(TRIM(CLEAN(AI2)),CHAR(160),""),SUBSTITUTE(TRIM(CLEAN(AI2)),CHAR(160),"")&"/"&SUBSTITUTE(TRIM(CLEAN(AJ2)),CHAR(160),""))
 
Upvote 0

Forum statistics

Threads
1,223,894
Messages
6,175,252
Members
452,623
Latest member
Techenthusiast

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