Question description:
This user has given permission to use the problem statement for this
blog.
I'm using the join function but want to trail off the final dash at the end if nothing is entered into a column
Solved by F. S. in 20 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
18/11/2017 - 12:49
Welcome to excel got it pro
Excelchat Expert
18/11/2017 - 12:50
I understand you want to join texts from two or more cells into one cell, right?
User
18/11/2017 - 12:50
Yep basically, I've gotten that part done
User
18/11/2017 - 12:50
but there is a trailing dash
Excelchat Expert
18/11/2017 - 12:50
Can you please tell me more about it?
User
18/11/2017 - 12:50
on cells where it is empty
User
18/11/2017 - 12:51
I'm just trying to get the formulas to reference
Excelchat Expert
18/11/2017 - 12:51
okay, tell me where it is.
User
18/11/2017 - 12:51
U
User
18/11/2017 - 12:51
column U
Excelchat Expert
18/11/2017 - 12:52
okay. so you want to join H, J, L N P R and T in that order with a dash between them right?
User
18/11/2017 - 12:53
Ok
User
18/11/2017 - 12:53
so see S7
User
18/11/2017 - 12:53
there is nothing there
User
18/11/2017 - 12:53
at the end of U7
User
18/11/2017 - 12:53
there is a trailing dash though
Excelchat Expert
18/11/2017 - 12:53
hmm
Excelchat Expert
18/11/2017 - 12:54
I see trailing dashes in each cells
User
18/11/2017 - 12:54
correct
User
18/11/2017 - 12:54
I only want them, if there is a value present
Excelchat Expert
18/11/2017 - 12:55
okay, there's another way to join texts by using "&" operator
User
18/11/2017 - 12:55
ah ok
Excelchat Expert
18/11/2017 - 12:55
let me show you.
User
18/11/2017 - 12:55
wasn't sure if there was an easier way but thats fine
User
18/11/2017 - 12:55
I can do that way
User
18/11/2017 - 12:55
I just like the join function because it is simpler!
User
18/11/2017 - 12:55
thanks
Excelchat Expert
18/11/2017 - 12:56
its easier , but join function has its own limitations
Excelchat Expert
18/11/2017 - 12:56
look at column V
Excelchat Expert
18/11/2017 - 12:57
I have just used two functions
Excelchat Expert
18/11/2017 - 12:57
sorry cells
Excelchat Expert
18/11/2017 - 12:57
now I will filter out dashed if its the last entry
Excelchat Expert
18/11/2017 - 12:59
now our T2 can be empty and in that case we will get a - at the end
Excelchat Expert
18/11/2017 - 01:01
=H2&"-"&J2&if(T2="","","-")&T2
Excelchat Expert
18/11/2017 - 01:03
see the formula? when we wanna put a dash we would check whether the next cell is empty or not, if empty then put nothing otherwise put a dash.
Excelchat Expert
18/11/2017 - 01:03
we do this for all the cells that we wanna join.
Excelchat Expert
18/11/2017 - 01:03
so let me write the whole formula that you would need
Excelchat Expert
18/11/2017 - 01:06
=H2&if(J2="","","-")&J2&if(L2="","","-")&L2&if(N2="","","-")&N2&if(P2="","","-")&P2&if(R2="","","-")&R2&if(T2="","","-")&T2
Excelchat Expert
18/11/2017 - 01:06
Here you go
Excelchat Expert
18/11/2017 - 01:06
are you there?
Excelchat Expert
18/11/2017 - 01:07
Well Please check the solution when you are back
Excelchat Expert
18/11/2017 - 01:08
Please re post again if you need more help
Excelchat Expert
18/11/2017 - 01:08
Thank you and Have a great day.
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
