• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Concatenate data that is missing.

bobhc

bobhc

Excel Ninja
Good day All

I am able to concatenate multiple cells and end up with John-Anybody-Smith with =CONCATENATE(G3,"-",H3,"-",I3),.........but I do not know how to do the coding to take into account that sometimes the list may not have any middle data, when the above is copied down I end up with this where there is no middle data John--Smith when I need John-Smith
 
Hi bobhc,


Hope you have the first name in G column, middle name in H column and Last name in I Column and you do the concatenation in J column.


With the help of 3 additional columns, I can get you the anwer.


Suppose your data is like below:


G1 = Test

H1 =

I1 = 2

J1 = Concatenate(G1, "-", H1, "-", I1)

Now I use 3 additiional columns to get the right answer for you...

K1 = =FIND("-", J1,1)

L1 = =FIND("-", J1, K1+1)

M1 = =IF(K1=(L1-1), REPLACE(J1, L1, 1, ""), J1)


Here, you can see the answer in the M column.


Hope it tunes fine for you.


Rgds,

KK
 
Good evening Kiran Kumar


Code works just as you said it would, bit of swapping around of cell names...........but that's down to me and the way the spread sheet is laid out. My thanks.


P.S what the blazes does == mean/do ????
 
Hi Bob,

I am again approaching you for UDF.. ;)


Create the Function as :

[pre]
Code: 
Function Concat(all As Range, del As String)
For Each cell In all
If cell.Value <> "" Then
Concat = Concat & del & cell.Text
End If
Next cell
Concat = Right(Concat, Len(Concat) - 1)
End Function
[/pre]
use Formula as

=Concat(A1:C1,"-")


where you can set customized Range and customized Delimeter..

In fact you can use =Concat(A1:C6,"*")..
 
Hi Bob ,


Can you see if this is OK ?


=CONCATENATE(G3,IF(AND(G3<>"",H3<>""),"-",""),H3,IF(AND(OR(G3<>"",H3<>""),I3<>""),"-",""),I3)


What this does is :


It inserts the first hyphen only if both G3 and H3 are present , since the first hyphen is supposed to be between G3 and H3.


It inserts the second hyphen if either G3 or H3 is present , and I3 is present , since the second hyphen is required between G3 and I3 , or between H3 and I3.


I think this covers all cases.


Narayan
 
Hi, b(ut)ob(ut)hc!


Good day, my friend.


My two cents...

=G3&IF(H3<>"","-","")&H3&"-"&I3


It only works for middle term (H3) missing, if others too, go for NARAYANK991's formula.


Regards!
 
Good day All

NARAYANK991 Your code works, does exactly what I wanted, concatenates perfectly, no matter what is missing, beginning/middle/end, I have blindly deleted data form different columns and rows, and your code does the job. You have my thanks.


SirJB7 Your code removes the same data as NARAYANK991’s code does with the exception that it leaves the hyphen. Perhaps you could open a can of Carlsberg and have another look, :)


Debraj Roy I am receiving a compile error “variable not defined”. I believe this may be due to the cell definition!
 
Good day shrivallabha

Your code give the exact same results as the one from SirJB7 in that it leaves behind the hypens
 
@ bobhc,

either Remove
Code: 
Option Explicit from top of the Module, or Dim Cell as Range
at first line of the Function.

It will work perfectly.. :)

Regards,

Deb
 
Debraj Roy

I am definitely doing some thing wrong I have tried the code via the sheet and in a module but no go, can not even step through in editor window?

[pre]
Code: 
Option Explicit
Function Concat(all As Range, del As String)
Dim Cell As Range
For Each Cell In all
If Cell.Value <> "" Then
Concat = Concat & del & Cell.Text
End If
Next Cell
Concat = Right(Concat, Len(Concat) - 1)
End Function
[/pre]

This is what I am putting in to the cell

=Concat(G1:I1,"-")
 
bobhc,


It is written for middle name missing case as you have stated specifically in your post 1. I am sure it fails when First or Last Name is missing and gives incorrect result. If other cases apply then please use Narayan's formula.


As an aside, a UDF needs to be placed in a module and not in a Sheet Module. You need to go to VBE window and do Insert | Module and then paste Debraj's code.
 
shrivallabha


I had tried both, sheet and module, I had played around with the ranges, and it wouls not work.

I deleted all sheet code and deleted the modules, this took some doing as the workbook seemed "locked". I then closed and reopened the workbook, reenters the code in to a module and and used the "Concat" range in last post, perfect it works, it works whether the is no data in beginning/middle/end.


Debraj Roy Took a bit of fiddling around but your code works, must have been Excel mucking me around. My thanks for you time and effort
 
Hi Bob!

Happy to help... and Glad that you solved!

If you ever need to increase the range from (3 to N) or you need change Delimiter ("-" to "any") you can use my Code, otherwise. Narayank formula was more preferable. :)

Regards,

Deb
 
Good day All

I have been trying out the advice I have received from all on this and one other post of mine, I think I have the "gist" of it all and I would appreciate any comments, this workbook is one most definitely for the library file.


upload https://dl.dropbox.com/u/75495784/Split_Cells.xlsm
 
Hi, b(ut)ob(ut)hc!


Good day, old dog.


Regarding my comment (http://chandoo.org/forums/topic/concatenate-data-that-is-missing#post-37819) and your comment (http://chandoo.org/forums/topic/concatenate-data-that-is-missing#post-37831), considering the clarification that was only working for middle part (I should say as you requested in your original post), I think it does the job... unless the specs were widen to all members of the trilogy, so you'd keep NARAYANK991's suggestion as I yet told you.


Regards!


PS: Going for another Carlsberg, but not checking again the code, unless confirmation of specs change :p
 
Hi Bob,


Its nice to find myself in comparison table.. feeling proud.. :)

So I have added on more row at the end.. Function TextToColumn..

In a expectation of hangover..


https://dl.dropbox.com/u/78831150/Excel/Split_Cells.xlsm

Regards,

Deb
 
Good day SirJB7 old friend


I realise that your code did as I requested at the beginning of the post and for that I thank you, but as the post has gone on different people have contributed their ideas and it has, for me, be a pleasant learning curve, by looking at the different ways I have learned more.
 
@b(ut)ob(ut)hc

Hi!

Good very late night or very early morning, my old friend.

I actually liked very much your workbook... if your library is full of workbooks so clearly and so didactically presented, you should be thinking about publishing them, either in paper or electronically.

Did I say publishing? Oh, yeah, I beg your pardon, I meant selling!

Regards!

PS: An idea flashed across my mind... I saw myself writing something like as "First of all welcome to OldDog's website Excel, Access & .NET forums..." :=)


@Debraj Roy

Hi!

Without having to hold a pen your hands are acquiring that fine and smooth touch that's... lovely.

Regards!
 
You must log in or register to reply here.
Back
Top