Concat formula for selected range of cell with specific separator

Dhavalshah
Lounger
Posts: 26
Joined: 25 Dec 2021, 07:33

Concat formula for selected range of cell with specific separator

Post by Dhavalshah »

Hi,

Can I get macro to use concat function for selected cells with specific separator (means each time it should ask which separator to be used between words like "," or "/" etc.).

Thanks

User avatar
HansV
Administrator
Posts: 78238
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Concat formula for selected range of cell with specific sperator

Post by HansV »

Should the macro place the result of the concatenation in a cell, or should it create a formula?
Best wishes,
Hans

Dhavalshah
Lounger
Posts: 26
Joined: 25 Dec 2021, 07:33

Re: Concat formula for selected range of cell with specific sperator

Post by Dhavalshah »

It should place result in cell.

User avatar
HansV
Administrator
Posts: 78238
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Concat formula for selected range of cell with specific sperator

Post by HansV »

Here is a macro:

Code: Select all

Sub ConcatenateValues()
    Dim strResult As String
    Dim strSeparator As String
    Dim rngSource As Range
    Dim rngTarget As Range
    Dim rngCell As Range
    On Error GoTo ErrHandler
    strSeparator = InputBox(Prompt:="Enter the separator", Default:=",")
    Set rngSource = Selection
    Set rngTarget = Application.InputBox(Prompt:="Select the output cell", Type:=8)
    Set rngTarget = rngTarget(1)
    For Each rngCell In rngSource
        If rngCell.Text <> "" Then
            strResult = strResult & strSeparator & rngCell.Text
        End If
    Next rngCell
    If strResult <> "" Then
        strResult = Mid(strResult, Len(strSeparator) + 1)
    End If
    rngTarget.Value = strResult
    Exit Sub
ErrHandler:
    Beep
End Sub
Best wishes,
Hans

Dhavalshah
Lounger
Posts: 26
Joined: 25 Dec 2021, 07:33

Re: Concat formula for selected range of cell with specific sperator

Post by Dhavalshah »

Thanks

snb
4StarLounger
Posts: 548
Joined: 14 Nov 2012, 16:06

Re: Concat formula for selected range of cell with specific sperator

Post by snb »

Code: Select all

Sub M_snb()
   For Each it In Selection
     c00 = c00 & Chr(0) & it
   Next
   c01 = InputBox("separator", , "_")
   
   Application.InputBox("Target", "", , , , , , 8) = Replace(Mid(c00, 2), Chr(0), c01)
End Sub

adeel1
3StarLounger
Posts: 264
Joined: 04 Oct 2017, 15:47

Re: Concat formula for selected range of cell with specific sperator

Post by adeel1 »

Code: Select all

Dim x1 As String
x1 = InputBox("Please enter Seperator", , ",")
Application.InputBox("Output", , , , , , , 8) = Join(Application.Transpose(Selection), x1)

snb
4StarLounger
Posts: 548
Joined: 14 Nov 2012, 16:06

Re: Concat formula for selected range of cell with specific sperator

Post by snb »

@adeel1

Your code only applies to a selection in 1 column, containing only 1 Area

adeel1
3StarLounger
Posts: 264
Joined: 04 Oct 2017, 15:47

Re: Concat formula for selected range of cell with specific sperator

Post by adeel1 »

Your code only applies to a selection in 1 column, containing only 1 Area
:thumbup: :thumbup:

User avatar
DocAElstein
4StarLounger
Posts: 546
Joined: 18 Jan 2022, 15:59
Location: Re-routing rivers, in Hof, Beautiful Bavaria

Re: Concat formula for selected range of cell with specific sperator

Post by DocAElstein »

@snb and @adeel1
Your macros don’t allow for empty cells as Hans one does, but they are cool anyway, especially adeel1’s non looping one, :thumbup:

Here is another non looping one. It uses something. I am not sure exactly what it uses anymore, whhich makes me sad, :( , but only a little bit... ... It either uses the ( Microsoft Windows ) Clipboard, or an interpreted representation of what is in the clipboard by the thing sometimes referred to as the Office clipboard which isn’t a clipboard but listens in to things put into or that would be later put into the ( Microsoft windows ) Clipboard … - I am not sure which of those two ( or something else ), it uses, and would be interested if anyone has an idea about that

But anyway, this clipboard or clipboard like representation of a range gets stored after a .Copy of a range in various version forms. ......
In one simple text values form:
Cells in a row are separated by a vbTab, ( so that single character is like the column seperator, I think of it as like the cell wall );
and rows are separated by a vbCr & vbLf, ( so those two characters together are like the row or line seperator) ;
and the cell text values are in between those things.

So a basic solution like I have here, is to get hold of that simple version form , change any vbCr & vbLf to a vbTab, then change all the vbTabs to the septicarator you want. Then Bingo, we have the final form you want.

Here is a full version that also allows for up to 3 consecutive empty cells. You can probably figure out how to extend that to 4 or as many as you think you might have

Code: Select all

Sub ClipboardOrNotClipboard_ThatIsTheQuestion() '
Rem 0 Ask for seperator
Dim Ceptix As String: Let Ceptix = VBA.InputBox("Septicarator", , ";")
Rem 1 Put data range in the clipboard
 Selection.Copy
Rem 2 get text data from the clipboard or from the interpretaion representation of it - the Office thing that might be called the Office clipboard
Dim objDataObject As Object: Set objDataObject = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") '    http://web.archive.org/web/20200124185244/http://excelmatters.com/2013/10/04/late-bound-msforms-dataobject/
Dim StringBack As String ' This has the entire text held ........ somewhere...... after a  .Copy
 objDataObject.GetFromClipboard: Let StringBack = objDataObject.GetText()
' Let StringBack = Left(StringBack, Len(StringBack) - 2) ' An extra vbCr & vbLf is added. I don't know why, maybe no one does? - see later instead ###
'                                                      Call WtchaGot_Unic_NotMuchIfYaChoppedItOff(StringBack)
Rem 3 manipulate string
 Let StringBack = Replace(StringBack, vbCr & vbLf, vbTab, 1, -1, vbBinaryCompare) ' Change the row/ line seperator for the cell/ column seperator
 Let StringBack = Replace(StringBack, vbTab & vbTab & vbTab, vbTab, 1, -1, vbBinaryCompare) ' ' This effectively gets rid of two consequeive empty cells
 Let StringBack = Replace(StringBack, vbTab & vbTab, vbTab, 1, -1, vbBinaryCompare) ' This effectively gets rid of an empty cell
 ' Working together, those two last lines above effectively also get rid of three consequetive empty cells, as the first line would effectively reduce any three to one, which the next line would catch
 Let StringBack = Replace(StringBack, vbTab, Ceptix, 1, -1, vbBinaryCompare) ' Change the row/ line seperator for the cell/ column seperator

 Let StringBack = Left(StringBack, Len(StringBack) - Len(Ceptix)) ' ### do this here instead to remove the added (vbCr & vbLf) after it has been converted to the vbTab and it will also take care of a last empty cell, since the resulting combination of vbTab & vbTab  ( instead of the original  vbTab & vbCr & vbLf  at the end  for the case of a last empty cell) will have been converted to a single vbTab , which along with all the others get replaced by the wanted Septicarator , and this lines gets rid of the last ( trailing )  unwanted one
Rem 4 put manipulated text in a cell next to the selection
 Let Selection.Resize(1, 1).Offset(0, Selection.Columns.Count).Value2 = StringBack
End Sub

Here is a more stripped down naked form. ( This also assumes that your separator is only 1 character. The full version allows you to have as many characters as you like in the separator,
but this version still allows for up to 3 consecutive empty cells )

Code: Select all

Sub SnbIt() ' Stripped down a bit
 Selection.Copy
    With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") '
    Dim StringBack As String
     .GetFromClipboard
     StringBack = .GetText()
    End With                         '
 StringBack = Replace(Replace(Replace(Replace(StringBack, vbCr & vbLf, vbTab), vbTab & vbTab & vbTab, vbTab), vbTab & vbTab, vbTab), vbTab, VBA.InputBox("separator", , ";"))
 Selection.Resize(1, 1).Offset(0, Selection.Columns.Count).Value2 = Left(StringBack, Len(StringBack) - 1) ' Assumes seperator is one character
End Sub
( I learnt something useful here: you can put a input box inside a long code line. Somehow I thought that should not have worked. Not sure why… )



( In both macro versions, the output is put in a cell – the next cell next to the selection )


Alan
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(

snb
4StarLounger
Posts: 548
Joined: 14 Nov 2012, 16:06

Re: Concat formula for selected range of cell with specific sperator

Post by snb »

@Doc,
Your macros don’t allow for empty cells as Hans one does
Are you kidding ?
TS didn't mention any empty cells.
You should be able to amend, if necessary at all:

Code: Select all

For Each it In Selection
  if it <>"" then c00 = c00 & Chr(0) & it
Next
especially adeel1’s non looping one
I hope you realize this assumes the selection is in 1 column only.
TS doesn't mention his/her selection is restricted to 1 column.

To help you further:

Code: Select all

Sub Snb_001()
    Selection.Copy
    With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") '
     .GetFromClipboard
     Selection.Cells(1).Offset(, Selection.Columns.Count + 1) = Replace(Application.Trim(Replace(Replace(Replace(.GetText, " ", "~"), vbCrLf, " "), vbTab, " ")), "~", " ")
    End With                         '
End Sub

User avatar
DocAElstein
4StarLounger
Posts: 546
Joined: 18 Jan 2022, 15:59
Location: Re-routing rivers, in Hof, Beautiful Bavaria

Re: Concat formula for selected range of cell with specific sperator

Post by DocAElstein »

Hello
…this assumes the selection is in 1 column only….. can do the Transpose a bit differently with Application.Index for more than one column…

Code: Select all

Sub SnberOne()  '   http://www.eileenslounge.com/viewtopic.php?p=301714&sid=4705abb7ec796b7a3426c78642d4f638#p301714
 Let Selection.Resize(1, 1).Offset(0, Selection.Columns.Count).Value2 = Join(Application.Index(Selection, Evaluate("=If({1},Int((Column(A:" & Split(Cells(1, Selection.Cells.Count).Address, "$")(1) & ")+(" & Selection.Columns.Count & "-1))/" & Selection.Columns.Count & "))"), Evaluate("=If({1},Mod((Column(A:" & Split(Cells(1, Selection.Cells.Count).Address, "$")(1) & ")-1),2)+1)")), VBA.InputBox("separator", , ";")) '      
End Sub
Rather Beautiful.
( Full story with explanations : https://excelfox.com/forum/showthread.php/2837-Appendix-Thread-App-Index-Rws()-Clms()-Majic-code-line-Codings-for-other-Threads-Tables-etc-)?p=19586&viewfull=1#post19586
https://excelfox.com/forum/showthread.php/2837-Appendix-Thread-App-Index-Rws()-Clms()-Majic-code-line-Codings-for-other-Threads-Tables-etc-)/page54#post19586
)

_._____

…To help you further:…. Yes that was helpful, Thx.
I can’t figure out a couple of things though, the first and last Replace:
Replace(______________, "~", " ")
Replace(.GetText, " ", "~")

As far as I can tell, they are red herrings, doing nothing? But I may have missed something, it would not be the first time Lol.

But the useful stuff I got from that is…
If, ( instead of replacing the vbCr & vbLf with vbTab as I did), we replace both the vbCr & vbLf and the vbTab with a space , " " , as you did, then we can use the Application.Trim to remove both the expected trailing space and any extra spaces caused by empty cells, as you demonstrated. Then all that is left to get a final solution is to replace the remaining single spaces with the separator.

I had not realised that the Application.Trim also takes out any spaces more than one within a string. So useful to learn that. I had previously only been familiar with the (… for want of a better description** the "not application" ..) Trim. (This other Trim only removes leading and trailing spaces.)

** I think possibly the Trim is an old VB thing? Perhaps the Application.Trim is a newer VBA thing? I don’t know.
Maybe we could call one the VB trim and the other the VBA trim?

Anyway, (ignoring for now the first and last "~ Replace ~ " bits), this would be a couple of solution examples using the replace with spaces and Application.Trim idea from snb

Code: Select all

 Sub SnufIt3()    ' http://www.eileenslounge.com/viewtopic.php?f=27&t=38973&p=301714#p301714
 Selection.Copy
Dim objDataObject As Object: Set objDataObject = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}") '    http://web.archive.org/web/20200124185244/http://excelmatters.com/2013/10/04/late-bound-msforms-dataobject/
Dim StringBack As String ' This has the entire text held ........ somewhere...... after a  .Copy
 objDataObject.GetFromClipboard: Let StringBack = objDataObject.GetText()
 Let StringBack = Replace(StringBack, vbCr & vbLf, " ")
 Let StringBack = Replace(StringBack, vbTab, " ")
                                                       'Let StringBack = Trim(StringBack) ' this only takes out leading and trailing spaces
 Let StringBack = Application.Trim(StringBack) ' This takes out leading and trailing spaces and also reduces all in between spaces to only one
 Let StringBack = Replace(Application.Trim(StringBack), " ", VBA.InputBox("separator", , ";"))
 Let Selection.Cells(1).Offset(0, Selection.Columns.Count + 1).Value2 = StringBack
End Sub
Sub SnufIt4()
 Selection.Copy
    With GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
    .GetFromClipboard
    Let Selection.Cells(1).Offset(0, Selection.Columns.Count + 1).Value2 = Replace(Application.Trim(Replace(Replace(.GetText(), vbCr & vbLf, " "), vbTab, " ")), " ", VBA.InputBox("separator", , ";"))
  End With
End Sub



Alan
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(

snb
4StarLounger
Posts: 548
Joined: 14 Nov 2012, 16:06

Re: Concat formula for selected range of cell with specific sperator

Post by snb »

The application.Trim is not a VBA element, it's an element of the Application (Excel in this case).
So, Application.Trim is an Excel Function, that can be called by the Excel VBA library.

User avatar
HansV
Administrator
Posts: 78238
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Concat formula for selected range of cell with specific seperator

Post by HansV »

For others reading this:

Trim is a VBA function. It takes a variant (string) as argument and returns that string with leading and trailing spaces removed.
TRIM is an Excel worksheet function. It also takes a variant (string) as argument, and returns that string with leading and trailing spaces removed, but also with multiple spaces within the string replaced with single spaces.
So

Code: Select all

Trim("  a    b   ")
in VBA returns

Code: Select all

a    b
but the cell formula

Code: Select all

=TRIM("  a    b  ")
returns

Code: Select all

a b
You can call the Excel worksheet function TRIM in your VBA code in two ways: using Application.WorksheetFunction.Trim and Application.Trim.

Code: Select all

MsgBox Application.Trim("  a    b  ")
will display

Code: Select all

a b
There is a slight difference between Application.WorksheetFunction.function and Application.function in the way they handle errors, but that is not really relevant to Trim.
Best wishes,
Hans

User avatar
DocAElstein
4StarLounger
Posts: 546
Joined: 18 Jan 2022, 15:59
Location: Re-routing rivers, in Hof, Beautiful Bavaria

Re: Concat formula for selected range of cell with specific seperator

Post by DocAElstein »

Ah yes, a pretty stupid things for me not to twig that the Application.Trim was an Excel worksheet function thing.
So, the Trim is a VBA thing, ( and possibly coming from earlier VB stuff), and of course the Application.Trim follows something similar to our recent discussions on the Application.Index function ( and Application.WorksheetFunction.Index function), which were clearly both the same Excel worksheet thing
( I think I made a reasonable attempt to explain the difference between Application.Index function and Application.WorksheetFunction.Index function, and the often quoted reason of the difference being the error handling was only part of the story.
I only have a few minutes experience so far with the Excel Trim thing used within VBA. I can’t think yet of anything that might show up the differences in what Application.Trim function and Application.WorksheetFunction.Trim function do or why? )
Thx for catching my stupidity there. My brain is a bit cold just now. (As is most of me. It's intersting this Naked Lounging, but does not suit the winter time so well)
I seriously don’t ever try to annoy. Maybe I am just the kid that missed being told about the King’s new magic suit, :(