convert 3 (Three) cell data into 1 (one) cell?
Author Message
convert 3 (Three) cell data into 1 (one) cell?

i have created a file datalist.xls with filed:-
& E-mail. i have more than 1000 data in it.
into i filed address is it possible to convert all this 3
cell into 1 cell?
any help.

Fri, 25 Nov 2005 10:43:47 GMT
convert 3 (Three) cell data into 1 (one) cell?
You can use either the concatenate function or by appling the "&" to join
all three togreather.

Using VBA you can do the following:

end sub

--- You will need to make sure that cell wrapping is on, otherwise you rill
just see a square where the Chr(10) is. Chr(10) sends the text to a new
line, otherwise, take it out of the code above. If you have merged cells,
the text won't autofit so keep that in mind.

If you don't want to use code, you can just put a formula to concatenate the
cells togeather:

Between the " " you need to press ALT + Enter to put a carriage return in.

HTH

Paul M.

Quote:
> i have created a file datalist.xls with filed:-
> & E-mail. i have more than 1000 data in it.
> into i filed address is it possible to convert all this 3
> cell into 1 cell?
> any help.

Fri, 25 Nov 2005 10:57:58 GMT
convert 3 (Three) cell data into 1 (one) cell?
You can use a function like this one in a normal module

' =RangeCat(A1:A10," ")  this in a worksheet cell
' from J.E. McGimpsey

Public Function RangeCat(rng As Excel.Range, _
Optional delimiter As String = "", _
Optional direction As Integer = 1) As Variant
Dim myColumn As Range
Dim cell As Range
If direction = 1 Then       'by rows
For Each cell In rng
RangeCat = RangeCat & delimiter & cell.Text
Next cell
ElseIf direction = 2 Then       'by cols
For Each myColumn In rng.Columns
For Each cell In myColumn.Cells
RangeCat = RangeCat & delimiter & cell.Text
Next cell
Next myColumn
Else
RangeCat = CVErr(xlErrNA)
Exit Function
End If
RangeCat = Mid(RangeCat, 1 + Len(delimiter))
End Function

Or use this

=D7&" " & E7 &" " & F7

--
Regards Ron de Bruin
(Win XP Pro SP-1  XL2002 SP-2)
www.rondebruin.nl

Quote:

> i have created a file datalist.xls with filed:-
> & E-mail. i have more than 1000 data in it.