VBA that combine selected cells in one cell and number them

Folsen

New Member
Joined
Dec 14, 2017
Messages
5
I have a huge spreadsheet that I'm trying to consolidate some lines in.
I have information in Column A, B, C and D, so I need to be able to select cells in any Column, but 1 at the time.

What I want to do is select 2 (or more) cells in Column A, and when I run the VBA it will:
- Concatenate the text from selected cells, in the first cell I selected, AND
- I want the text from each cell to be in it's separate line (same as the Alt+Enter), AND
- I want to add "a)", "b)", etc. in front of the text of each cell.

Original Data can look like this (data in 4 separate cells):
Audi - is German
Bentley - is expensive
Chevy - is a solid car
Dodge - have a long history with Pickup Trucks

<tbody>
</tbody>



Data after I run the VBA, look like this (Data is now in 1 cell w/alphabetic bullet/numbering);
a) Audi - is German
b) Bentley - is expensive
c) Chevy - is a solid car
d) Dodge - have a long history with Pickup Trucks

<tbody>
</tbody>
 

Some videos you may like

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,630
Welcome to the forum.

Try:
Code:
Sub ConcatAndOrg()
Dim ctr As Long, str1 As String, c As Range

    ctr = 0
    str1 = ""
    For Each c In Selection
        ctr = ctr + 1
        str1 = str1 & LCase(Replace(Cells(1, ctr).Address(0, 0), 1, "")) & ") " & c.Value & vbCrLf
    Next c
    Selection.ClearContents
    Selection.Cells(1, 1).Value = str1
    
End Sub
If you select cells A1:A3, A7 from this layout, column A will end up looking like column C here:
ABC
1Audi - is Germana) Audi - is German
b) Bentley - is expensive
c) Chevy - is a solid car
d) Dodge - have a long history with Pickup Trucks
2Bentley - is expensive
3Chevy - is a solid car
4Edsel - long goneEdsel - long gone
5Ford - another car companyFord - another car company
6BMW - also expensiveBMW - also expensive
7Dodge - have a long history with Pickup Trucks

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet3



Also note that when you say you want it in the first cell you select, there's really no way to tell that. Excel helpfully organizes the selected range for you. So I just put the results in the upper left corner of the range.

I'd assume you'd want to open the macro selector and assign a hot-key to this macro to make it easier to run.

Let me know if this works for you.
 

Folsen

New Member
Joined
Dec 14, 2017
Messages
5
That is AWSOME :) works great! Thanks a lot for your help Eric :)

I don't understand VBA, but I am starting to learn because it's such a great tool.

Can I ask if you could help out with one more?
If I select 2 or more (same as before), consolidate to 1 cell, but use "Strikethrough" for all but the first?
Is that possible?
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,630
Glad it works for you! The strikethrough part just takes a little tweak:

Code:
Sub ConcatAndStrike()
Dim ctr As Long, str1 As String, c As Range, beg As Long

    ctr = 0
    str1 = ""
    For Each c In Selection
        ctr = ctr + 1
        str1 = str1 & LCase(Replace(Cells(1, ctr).Address(0, 0), 1, "")) & ") " & c.Value & vbCrLf
        If ctr = 1 Then beg = Len(str1) + 1
    Next c
    Selection.ClearContents
    Selection.Cells(1, 1).Value = str1
    Selection.Cells(1, 1).Characters(Start:=beg, Length:=Len(str1)).Font.Strikethrough = True
    
End Sub
 

Folsen

New Member
Joined
Dec 14, 2017
Messages
5

ADVERTISEMENT

Works like a charm!! :) :)
Just one thing, I do not need the a), b), etc. here. Sorry for not specifying that.
 

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,630
No problem! Just change this line:

Code:
str1 = str1 & LCase(Replace(Cells(1, ctr).Address(0, 0), 1, "")) & ") " & c.Value & vbCrLf

to

Code:
str1 = str1 & c.Value & vbCrLf

:cool:
 

Watch MrExcel Video

Forum statistics

Threads
1,122,906
Messages
5,598,783
Members
414,259
Latest member
beetle12

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top