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 |
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 . |
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 |
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 |
All times are GMT +1. The time now is 07:00 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com