If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
merge
Hi
I have a row of words each in separte cells, I want to merge all of them into 1 cell. thanks |
#2
|
|||
|
|||
merge
Try the below formula
=A1&B1&C1&D1 If you dont want to write this formula try the below UDF (User Defined function). From workbook launch VBE using Alt+F11. From menu Insert a Module and paste the below function.Close and get back to workbook and try the below formula. Syntax: =CONCATRANGE(rngRange,strDelimiter,blnIgnoreBlank) rngRange is the Range strDelimiter Optional . Default is space blnIgnoreBlank Optional. Default is False Examples: '1. Concatenate with default delimiter(space) =CONCATRANGE(A1:A10) '2. Concatenate with semicolon as delimiter and ignore blanks =CONCATRANGE(A1:A10,":",1) Function CONCATRANGE(rngRange As Range, _ Optional strDelimiter As String = " ", _ Optional blnIgnoreBlank As Boolean = False) Dim varTemp As Range For Each varTemp In rngRange If blnIgnoreBlank Then If Trim(varTemp) vbNullString Then _ CONCATRANGE = CONCATRANGE & strDelimiter & varTemp Else CONCATRANGE = CONCATRANGE & strDelimiter & varTemp End If Next CONCATRANGE = _ WorksheetFunction.Trim(Mid(CONCATRANGE, len(strDelimiter)+1)) End Function -- Jacob "PL" wrote: Hi I have a row of words each in separte cells, I want to merge all of them into 1 cell. thanks |
#3
|
|||
|
|||
merge
Presupposing your words are in column A stating at row 1 and continuing down
thro' rows 2 onwards Use a blank cell say B1 and use this formula: =A1&" "&A2&" "&A3&" "&A4 and so on. Change the cell references to suit where the words are. The &" "& will create a space between each word or group of words in each cell. Hope this helps. "PL" wrote: Hi I have a row of words each in separte cells, I want to merge all of them into 1 cell. thanks |
Thread Tools | |
Display Modes | |
|
|