"SETSTRING" Function in Lotus 123

[Guest]

There is a function in Lotus 123 called SETSTRING which allows you to take data from multiple cells and combine them into a one cell string of text. You can also set the number of spaces between each label in the string. I am unable to find the equivalent function in EXCEL. If there is anyone out there that knows the equivalent or another method of duplicating this function in would much appreciated.

 

[Dave R.]

=CONCATENATE(A1," ",B1," ",C1)

or

=A1&" "&B1&" "&C1

There is a function in Lotus 123 called SETSTRING which allows you to take

data from multiple cells and combine them into a one cell string of text.
You can also set the number of spaces between each label in the string. I
am unable to find the equivalent function in EXCEL. If there is anyone out
there that knows the equivalent or another method of duplicating this
function in would much appreciated.

 

[Harlan Grove]

There is a function in Lotus 123 called SETSTRING which allows you to take
data from multiple cells and combine them into a one cell string of text.
You can also set the number of spaces between each label in the string. I am
unable to find the equivalent function in EXCEL. If there is anyone out
there that knows the equivalent or another method of duplicating this
function in would much appreciated.

I only have 123R97 and 123R5 on this machine, so @SETSTRING may have changed in
later versions, but in R97 it doesn't combine text from multiple cells itself,
but multiple calls to it along with the & operator could be used to do this. In
terms of how it seems to work,

@SETSTRING("foo",10,0) == "foo "
=LEFT("foo"&REPT(" ",10),10)

@SETSTRING("foo,10,1) == " foo "
=MID(REPT(" ",10)&"foo"&REPT(" ",10),1+(10+LEN("foo"))/2,10)

@SETSTRING("foo",10,2) == " foo"
=RIGHT(REPT(" ",10)&"foo",10)

These aren't robust. If the length of the 1st argument to @SETSTRING exceeds the
length of the 2nd, @SETSTRING returns its 1st argument in whole. If you need
that functionality, the Excel equivalents become, respectively,

=LEFT("foo"&REPT(" ",10),MAX(LEN("foo"),10))

=MID(REPT(" ",10)&"foo"&REPT(" ",10),1+(10+MIN(LEN("foo"),10))/2,
MAX(LEN("foo"),10))

=RIGHT(REPT(" ",10)&"foo",MAX(LEN("foo"),10))

 

[Harlan Grove]

Reposted with full subject line.

There is a function in Lotus 123 called SETSTRING which allows you to take
data from multiple cells and combine them into a one cell string of text.
You can also set the number of spaces between each label in the string. I am
unable to find the equivalent function in EXCEL. If there is anyone out
there that knows the equivalent or another method of duplicating this
function in would much appreciated.

I only have 123R97 and 123R5 on this machine, so @SETSTRING may have changed in
later versions, but in R97 it doesn't combine text from multiple cells itself,
but multiple calls to it along with the & operator could be used to do this. In
terms of how it seems to work,

@SETSTRING("foo",10,0) == "foo "
=LEFT("foo"&REPT(" ",10),10)

@SETSTRING("foo,10,1) == " foo "
=MID(REPT(" ",10)&"foo"&REPT(" ",10),1+(10+LEN("foo"))/2,10)

@SETSTRING("foo",10,2) == " foo"
=RIGHT(REPT(" ",10)&"foo",10)

These aren't robust. If the length of the 1st argument to @SETSTRING exceeds the
length of the 2nd, @SETSTRING returns its 1st argument in whole. If you need
that functionality, the Excel equivalents become, respectively,

=LEFT("foo"&REPT(" ",10),MAX(LEN("foo"),10))

=MID(REPT(" ",10)&"foo"&REPT(" ",10),1+(10+MIN(LEN("foo"),10))/2,
MAX(LEN("foo"),10))

=RIGHT(REPT(" ",10)&"foo",MAX(LEN("foo"),10))

 

[Ron Rosenfeld]

There is a function in Lotus 123 called SETSTRING which allows you to take data from multiple cells and combine them into a one cell string of text. You can also set the number of spaces between each label in the string. I am unable to find the equivalent function in EXCEL. If there is anyone out there that knows the equivalent or another method of duplicating this function in would much appreciated.

I'm not aware of any such function in Excel. You can use CONCATENATE (or &
operator equivalent) but you have to specify the <space> between each and every
cell.

You can easily write a User Defined Function to accomplish the same thing,
however. Something like:

=====================================
Option Explicit
Function SetString(SpacesBetween As Integer, _
ParamArray rg() As Variant) As String
Dim c As Variant
Dim i As Long

For i = 0 To UBound(rg)
Select Case VarType(rg(i))
Case Is = vbArray + vbVariant
For Each c In rg(i)
SetString = SetString & Space(SpacesBetween) & c
Next
Case Is = vbString
SetString = SetString & Space(SpacesBetween) & rg(i)
End Select
Next i

SetString = Trim(SetString)

End Function
====================================

might do what you want.

To enter it, <alt-F11> opens the Visual Basic Editor.
Ensure that your current project is highlighted in the project explorer window;
then Insert/Module and paste the above code into the window that opens.

To use the function, in some cell enter:

=SETSTRING(spaces,<one or more cells, cell ranges, or array constants>)

For example, given:

$J$2 ab
$J$3 cd
$J$4 ef
$J$5 gh
$J$7 ij
$G$7 kl


the formula: =setstring(2,J2:J5,J7,G7,{1,2,3,"The End"})

will return: ab cd ef gh ij kl 1 2 3 The End
(two spaces between each entry)




=setstring(2,J2:J5,J7,G7,{1,2,3,"The End"})


--ron