Forum Discussion

mfseingim's avatar
mfseingim
Copper Contributor
Sep 10, 2023

How to convert more colums into a single one separated by ";"

Hi to everyone,

here's an example of what I want to do:

 

I have three columns witch each three rows:

 

a1         b1          c1

a2         b2          c2

a3         b3          c3

 

I want to convert them into a single column, with the values separated by ";":

 

a1;b1;c1

a2;b2;c2

a3;b3;c3

 

How can I do it?

Thank you very much.

excel
  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Sep 11, 2023

    mfseingim 

    If you're going to go with a LAMBDA, you might as well take it a bit further and make the source dynamic.

     

    MyRange = A1:C10000

    =LET(
    r, COUNTA(TAKE(MyRange, , 1)),
    dynamic, TAKE(MyRange, r),
    Join, LAMBDA(row, TEXTJOIN(";", , row)),
    BYROW(dynamic, Join)
)

     

     

     

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Sep 10, 2023

    mfseingim 

    I take the solution a bit further than Patrick2788

    Rather than creating a fill-down formula with relative referencing, I aim to create a single function that returns the entire list.  Naming your data, 'dataArray', the processing may be done row by row using

    = BYROW(dataArray, Joinλ)

    where Joinλ is a slightly more restrictive Lambda version of TEXTJOIN

    = LAMBDA(x, TEXTJOIN(";", , x))

    I would upload a picture but, apparently I can no longer be allowed to upload pictures.

     

    • SergeiBaklan's avatar
      SergeiBaklan
      MVP
      Sep 10, 2023

      PeterBartholomew1 

      To play with Python for Excel

      import numpy
df = xl("dataArray", headers = False )
rows = numpy.matrix(df).tolist()
list(map(lambda x : '; '.join(x), rows) )

      Result is the same.

      • PeterBartholomew1's avatar
        PeterBartholomew1
        Silver Contributor
        Sep 10, 2023

        SergeiBaklan 

        I have still to come to terms with this (I need to put some time aside).  Does the access to Python libraries augment the world of spreadsheet formulas or replace it?  For a hard-core Python programmer, I guess it is the latter, but do they need Excel and the worksheet environment.

        I can see some places where rolling one's own formulas is verging on being too difficult (extracting eigenvalues for example) and a well-written library routine would inspire greater confidence.  At the moment, though, I have so much more from the Excel formula language, particularly as it includes Lambda functions, than I might ever have dreamt of having to hand.

Resources