Add bullet points within a cell

[BRABUS]

Hello, ok so after a lot of trying myself and also searching I have decided to admit defeat and ask for help.

What I want to do is go down each cell in the table I have in column S only (excluding the first row, the header) and to

1. go through each line within the cell in turn and add bullet points to the line.
2. If there is a line of zero length (no actual text) then to delete it.
3. Finally sort the remaining lines in alphabetical order.

I have been working on this myself but I don’t know why, when it all looks good and what I am expecting, randomly when I next go and select the cell to view or edit it that lots of blank lines have been added, I was struggling with the same issue when I had been adding bullet points manually, this is how I started down the VBA route.

Grateful for anyone who can help me out here, my hair is almost completely white now LOL.

 

[ExcelGoogler]

I have so many questions. Maybe it would help to start by sharing the file you've worked on with the issue you've encountered.

One of the biggest questions is, why would you try to work with multiple lines in one cell?

 

[BRABUS]

I have so many questions. Maybe it would help to start by sharing the file you've worked on with the issue you've encountered.

One of the biggest questions is, why would you try to work with multiple lines in one cell?

Due to the nature of my work I can’t share the file.

I do agree though about multiple lines in a single cell but it’s what I have inherited and getting people here to change is hard work…..no, almost impossible.

 

[NoSparks]

@BRABUS,
We don't need the exact file you are working with.
Mock up a dummy file that is at least indicative of what that S column is before adding bullets and what's wanted after adding the bullets.
As is right now nobody but you has any idea as to what you're working with so any suggestion would just be guessing.
I can't speak for anyone else but if I have to guess I'm not going to bother.

 

[p45cal]

Guessing a lot. You can use a formula to get a result in another cell; this one looks at cell S2:

=TEXTJOIN(CHAR(10),TRUE,"•"&SORT(TEXTSPLIT(S2,,CHAR(10),TRUE)))

Convert that to a spilling formula that works on a column of cells, S2:S5 here:

=IFERROR(BYROW(S2:S5,LAMBDA(a,TEXTJOIN(CHAR(10),TRUE,"•"&SORT(TEXTSPLIT(a,,CHAR(10),TRUE))))),"")

VBA code to convert a range of cells in situ:
Call the macro with the likes of

blah Range("Q3:Q5")

which calls this:

Sub blah(myRng)
For Each cll In myRng.Cells
  xs = Split(cll.Value, Chr(10))
  If UBound(xs) <> -1 Then
    ReDim Newxs(0 To UBound(xs))
    idx = -1
    For Each x In xs
      If Trim(x) <> "" Then
        idx = idx + 1
        Newxs(idx) = "•" & x
      End If
    Next x
    ReDim Preserve Newxs(0 To idx)
    Newxs = Application.Sort(Newxs, , , True)
    result = Join(Newxs, Chr(10))
    cll.Value = result
  End If
Next cll
End Sub

 

[p45cal]

…or call this instead:

Sub blah(myRng)
For Each cll In myRng
  cll.value = Evaluate("TEXTJOIN(CHAR(10),TRUE,""•"" & SORT(TEXTSPLIT(" & cll.Address(external:=True) & ",,CHAR(10),TRUE)))")
  'or, to treat lines which have only space characters as totally blank lines:
  'cll.value = Evaluate("LET(a,TRIM(TEXTSPLIT(" & cll.Address(external:=True) & ",,CHAR(10),TRUE)),TEXTJOIN(CHAR(10),TRUE,""•"" & SORT(FILTER(a,a<>""""))))")
Next cll
End Sub

 

[BRABUS]

…or call this instead:

Sub blah(myRng)
For Each cll In myRng
  cll.value = Evaluate("TEXTJOIN(CHAR(10),TRUE,""•"" & SORT(TEXTSPLIT(" & cll.Address(external:=True) & ",,CHAR(10),TRUE)))")
  'or, to treat lines which have only space characters as totally blank lines:
  'cll.value = Evaluate("LET(a,TRIM(TEXTSPLIT(" & cll.Address(external:=True) & ",,CHAR(10),TRUE)),TEXTJOIN(CHAR(10),TRUE,""•"" & SORT(FILTER(a,a<>""""))))")
Next cll
End Sub

@p45cal thank you I like that, it is very simple yet effective.

Are you able to help with the other bits please? Although I think the steps and their order need rethinking, I have redone them below.

1. If a cell is blank move to next cell.
2. If the cell isn’t blank then go through each line within the cell in turn
   1. If there are bullet points then remove them all.
   2. If there is a line of zero length (no actual text, include lines with just spaces) then to delete it.
   3. Sort the remaining lines in alphabetical order.
   4. Lastly and add bullet points back to each remaining line.

I hope you can help me with this please. Then I can try and work out and UNDERSTAND how you did it. I don’t want to be one of these people who just wants the answer given to them.

Thanks

 

[p45cal]

Are you able to help with the other bits please?

The 3 bits you mentioned in your first message have already been addressed; those things all pertained to what happens within a cell.
To address the new things, the code below:
1. skips blank cells
2.1. removes all bullets wherever they are in the cell (including those not at the beginnng of a line)
2.2. already addressed with the commented-out line of my previous answer
2.3. already addressed
2.4. already addressed

Sub blah(myRng)
For Each cll In myRng
  If Application.Trim(cll.value) <> "" Then
      cll.value = Evaluate("SUBSTITUTE(" & cll.Address(external:=True) & ",""•"","""")")
      cll.value = Evaluate("LET(a,TRIM(TEXTSPLIT(" & cll.Address(external:=True) & ",,CHAR(10),TRUE)),TEXTJOIN(CHAR(10),TRUE,""•"" & SORT(FILTER(a,a<>""""))))")
  End If
Next cll
End Sub

 

[BRABUS]

@p45cal i owe you an apology. I had read your response on my phone and only seen the 2nd bit where you had said …or call this instead.

Your code works beautifully thank you. Now to go a test it in anger on my spreadsheet, this will show if it is an odd issue in excel or something related to my work machine.