Concat formula for selected range of cell with specific separator
-
- Lounger
- Posts: 26
- Joined: 25 Dec 2021, 07:33
Concat formula for selected range of cell with specific separator
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
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
-
- Administrator
- Posts: 78409
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Concat formula for selected range of cell with specific sperator
Should the macro place the result of the concatenation in a cell, or should it create a formula?
Best wishes,
Hans
Hans
-
- Lounger
- Posts: 26
- Joined: 25 Dec 2021, 07:33
Re: Concat formula for selected range of cell with specific sperator
It should place result in cell.
-
- Administrator
- Posts: 78409
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Concat formula for selected range of cell with specific sperator
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
Hans
-
- Lounger
- Posts: 26
- Joined: 25 Dec 2021, 07:33
-
- 4StarLounger
- Posts: 574
- Joined: 14 Nov 2012, 16:06
Re: Concat formula for selected range of cell with specific sperator
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
-
- 3StarLounger
- Posts: 264
- Joined: 04 Oct 2017, 15:47
Re: Concat formula for selected range of cell with specific sperator
Code: Select all
Dim x1 As String
x1 = InputBox("Please enter Seperator", , ",")
Application.InputBox("Output", , , , , , , 8) = Join(Application.Transpose(Selection), x1)
-
- 4StarLounger
- Posts: 574
- Joined: 14 Nov 2012, 16:06
Re: Concat formula for selected range of cell with specific sperator
@adeel1
Your code only applies to a selection in 1 column, containing only 1 Area
Your code only applies to a selection in 1 column, containing only 1 Area
-
- 3StarLounger
- Posts: 264
- Joined: 04 Oct 2017, 15:47
Re: Concat formula for selected range of cell with specific sperator
Your code only applies to a selection in 1 column, containing only 1 Area
-
- 4StarLounger
- Posts: 580
- 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
@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,
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
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 )
( 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
Your macros don’t allow for empty cells as Hans one does, but they are cool anyway, especially adeel1’s non looping one,
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
( 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, :(
-
- 4StarLounger
- Posts: 574
- Joined: 14 Nov 2012, 16:06
Re: Concat formula for selected range of cell with specific sperator
@Doc,
TS didn't mention any empty cells.
You should be able to amend, if necessary at all:
TS doesn't mention his/her selection is restricted to 1 column.
To help you further:
Are you kidding ?Your macros don’t allow for empty cells as Hans one does
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
I hope you realize this assumes the selection is in 1 column only.especially adeel1’s non looping one
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
-
- 4StarLounger
- Posts: 580
- 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
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…
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
Alan
…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
( 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, :(
-
- 4StarLounger
- Posts: 574
- Joined: 14 Nov 2012, 16:06
Re: Concat formula for selected range of cell with specific sperator
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.
So, Application.Trim is an Excel Function, that can be called by the Excel VBA library.
-
- Administrator
- Posts: 78409
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Concat formula for selected range of cell with specific seperator
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 in VBA returns but the cell formula returns
You can call the Excel worksheet function TRIM in your VBA code in two ways: using Application.WorksheetFunction.Trim and Application.Trim.
will display
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.
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 ")
Code: Select all
a b
Code: Select all
=TRIM(" a b ")
Code: Select all
a b
Code: Select all
MsgBox Application.Trim(" a b ")
Code: Select all
a b
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 580
- 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
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)
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, :(