EXCEL - Create a bulleted list in a single cell from a list
Solved/Closed
morocog
Posts
2
Registration date
Thursday 6 June 2013
Status
Member
Last seen
12 June 2013
-
10 Jun 2013 à 15:43
TrowaD Posts 2921 Registration date Sunday 12 September 2010 Status Contributor Last seen 27 December 2022 - 13 Jun 2013 à 10:22
TrowaD Posts 2921 Registration date Sunday 12 September 2010 Status Contributor Last seen 27 December 2022 - 13 Jun 2013 à 10:22
Related:
- EXCEL - Create a bulleted list in a single cell from a list
- Counter strike 1.6 cheats list - Guide
- Convert the bulleted list to a smartart graphic - Guide
- Epic games free games list - Guide
- Whatsapp country code list - Guide
- Phone contacts list - Guide
1 response
TrowaD
Posts
2921
Registration date
Sunday 12 September 2010
Status
Contributor
Last seen
27 December 2022
555
11 Jun 2013 à 11:21
11 Jun 2013 à 11:21
Hi Morocog,
Separated by commas the formula would look like:
=IF(A1="","",A1)&IF(A2="","",","&A2)&IF(A3="","",","&A3)&IF(A4="","",","&A4)&IF(A5="","",","&A5)&IF(A6="","",","&A6)&IF(A77="","",","&A7)&IF(A8="","",","&A8)&IF(A9="","",","&A9)&IF(A10="","",","&A10)
For the bulleted list we will have to use macro:
Why would you want to put more info into a single cell? This will make it harder to work with the data. Just curious what the reason could be.
Best regards,
Trowa
Separated by commas the formula would look like:
=IF(A1="","",A1)&IF(A2="","",","&A2)&IF(A3="","",","&A3)&IF(A4="","",","&A4)&IF(A5="","",","&A5)&IF(A6="","",","&A6)&IF(A77="","",","&A7)&IF(A8="","",","&A8)&IF(A9="","",","&A9)&IF(A10="","",","&A10)
For the bulleted list we will have to use macro:
Sub CreateBulletListInD5()
If Range("A1").Value <> vbNullString Then Range("D5").Value = Range("A1").Value
If Range("D5").Value <> vbNullString And Range("A2").Value <> vbNullString Then Range("D5").Value = Range("D5").Value & Chr(10)
If Range("A2").Value <> vbNullString Then Range("D5").Value = Range("D5").Value & Range("A2").Value
If Range("D5").Value <> vbNullString And Range("A3").Value <> vbNullString Then Range("D5").Value = Range("D5").Value & Chr(10)
If Range("A3").Value <> vbNullString Then Range("D5").Value = Range("D5").Value & Range("A3").Value
If Range("D5").Value <> vbNullString And Range("A4").Value <> vbNullString Then Range("D5").Value = Range("D5").Value & Chr(10)
If Range("A4").Value <> vbNullString Then Range("D5").Value = Range("D5").Value & Range("A4").Value
If Range("D5").Value <> vbNullString And Range("A5").Value <> vbNullString Then Range("D5").Value = Range("D5").Value & Chr(10)
If Range("A5").Value <> vbNullString Then Range("D5").Value = Range("D5").Value & Range("A5").Value
If Range("D5").Value <> vbNullString And Range("A6").Value <> vbNullString Then Range("D5").Value = Range("D5").Value & Chr(10)
If Range("A6").Value <> vbNullString Then Range("D5").Value = Range("D5").Value & Range("A6").Value
If Range("D5").Value <> vbNullString And Range("A7").Value <> vbNullString Then Range("D5").Value = Range("D5").Value & Chr(10)
If Range("A7").Value <> vbNullString Then Range("D5").Value = Range("D5").Value & Range("A7").Value
If Range("D5").Value <> vbNullString And Range("A8").Value <> vbNullString Then Range("D5").Value = Range("D5").Value & Chr(10)
If Range("A8").Value <> vbNullString Then Range("D5").Value = Range("D5").Value & Range("A8").Value
If Range("D5").Value <> vbNullString And Range("A9").Value <> vbNullString Then Range("D5").Value = Range("D5").Value & Chr(10)
If Range("A9").Value <> vbNullString Then Range("D5").Value = Range("D5").Value & Range("A9").Value
If Range("D5").Value <> vbNullString And Range("A10").Value <> vbNullString Then Range("D5").Value = Range("D5").Value & Chr(10)
If Range("A10").Value <> vbNullString Then Range("D5").Value = Range("D5").Value & Range("A10").Value
End Sub
Why would you want to put more info into a single cell? This will make it harder to work with the data. Just curious what the reason could be.
Best regards,
Trowa
12 Jun 2013 à 10:44
The reason why I need this bulleted list is because it is the final result of a series of formulas working with text and I need a summary, in one cell, that could be printed (without the need of working with that data anymore), I know how odd it sounds. You were really helpful, both the formula and the macro worked like a charm.
13 Jun 2013 à 10:22