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
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

nardagus

Active Member
Joined
Apr 23, 2012
Messages
314
Office Version
2016, 2013
Platform
Windows
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

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

nardagus

Active Member
Joined
Apr 23, 2012
Messages
314
Office Version
2016, 2013
Platform
Windows
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
2WhiteRoyal RedWhite/Royal Red

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

Worksheet Formulas
CellFormula
AK2=AI2&"/"&AJ2

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 

colarguns

New Member
Joined
Jul 2, 2016
Messages
25
Tried that. Result is White/ Royal Red
Notice the space between / and Royal? That is the issue I am having.
 

colarguns

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

colarguns

New Member
Joined
Jul 2, 2016
Messages
25
You are hereby awarded the MS Excel Pulitzer Award of the Century! That worked great!!!!! Thank you so much Sandy!
 

sandy666

Well-known Member
Joined
Oct 24, 2015
Messages
5,006
Extended version: =IF(AJ2="",SUBSTITUTE(TRIM(CLEAN(AI2)),CHAR(160),""),SUBSTITUTE(TRIM(CLEAN(AI2)),CHAR(160),"")&"/"&SUBSTITUTE(TRIM(CLEAN(AJ2)),CHAR(160),""))
 

Watch MrExcel Video

Forum statistics

Threads
1,095,230
Messages
5,443,259
Members
405,220
Latest member
gtgaabaron

This Week's Hot Topics

  • Copy entire row if CountA <>0 to another sheet
    [B]I want to copy entire row if CountA <>0 for column J7:AM7 (headers on J6:AM6) and so on till the last used cell is column D and paste the...
  • Select last used Row in Table
    I have created a Table in a Worksheet which is locked to prevent user errors and protect formula. Some of the cells require freetext entries which...
  • excel workbook: do not allow certain file name
    Hello all, Don't think this has ever been asked before, but how do I restrict file save [Before_Save Event] if the name of the file being saved...
  • fixing problem autofilter
    hello i need help about my code when i search by code in textbox it doesn't show anything this is my data [ATTACH type="full"...
  • “Weight”
    Hi, i’ve got a long sheet filled with weights such as kg,g,L & ml. i can build a formula to convert kg into g and liter into ml. How ever, my...
  • How to capitalize everything before a certain character?
    In column A, I have some text: Hello good day.mp3 Hello good day.flac etc. I'd like to capitalize everything before the period. I don't need the...
Top