Home |
Search |
Today's Posts |
#1
|
|||
|
|||
merge data
help me please.
How can I merge data from range a to z and the result will be on column ab without typing =a1&b1&..... is there a better way doing it? Thanks |
#2
|
|||
|
|||
Use function concatenate()
-----Original Message----- help me please. How can I merge data from range a to z and the result will be on column ab without typing =a1&b1&..... is there a better way doing it? Thanks . |
#3
|
|||
|
|||
Hi
Here is a basic function. Copy it into a module in the VB editor (ALT&F11). Function BigConcat(data) Dim c As Variant, str As String For Each c In data str = str & c.Value Next BigConcat = str End Function Remember though excel only displays a limited number of characters, 256 I think. Regards Peter "mhei" wrote: help me please. How can I merge data from range a to z and the result will be on column ab without typing =a1&b1&..... is there a better way doing it? Thanks |
#4
|
|||
|
|||
Macro with your choice of de-limiter if desired. If no de-limiter required,
do not enter anything in inputbox. Sub ConCat_Cells() Dim x As Range Dim y As Range Dim z As Range Dim w As String Dim sbuf As String On Error GoTo endit w = InputBox("Enter the Type of De-limiter Desired") Set z = Application.InputBox("Select Destination Cell", _ "Destination Cell", , , , , , 8) Application.SendKeys "+{F8}" Set x = Application.InputBox _ ("Select Cells...Contiguous or Non-Contiguous", _ "Cells Selection", , , , , , 8) For Each y In x If Len(y.text) 0 Then sbuf = sbuf & y.text & w Next z = Left(sbuf, Len(sbuf) - 1) Exit Sub endit: MsgBox "Nothing Selected. Please try again." End Sub OR a UDF with no de-limiter. Function ConRange(CellBlock As Range) As String Application.Volatile True For Each Cell In CellBlock ConRange = ConRange & Cell.Value Next End Function Usage is: =ConRange(A1:Z1) Note: you will see about 1024 characters maximum in the cell. Gord Dibben Excel MVP On Mon, 11 Apr 2005 11:04:38 -0700, "mhei" wrote: help me please. How can I merge data from range a to z and the result will be on column ab without typing =a1&b1&..... is there a better way doing it? Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pulling data from 1 sheet to another | Excel Worksheet Functions | |||
To safety merge cells without data destroyed, and smart unmerge! | Excel Discussion (Misc queries) | |||
Extract Data for Mail Merge | Excel Discussion (Misc queries) | |||
merge data problems | Excel Worksheet Functions | |||
how do i merge 2 rows of data into one | Excel Discussion (Misc queries) |