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:-
Name, co-name,addline1,addline2,addline3, city, pin, Tel.,
& E-mail. i have more than 1000 data in it.
i want to convert all addline1, addline2 & allline3 data
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:

sub ReJigMyAdd()
dim add1 as string
dim add2 as string
dim add3 as string
dim outadd as string

add1 = worksheets("sheet 1").range("addline1").value
add2 = worksheets("sheet 1").range("addline2").value
add3 = worksheets("sheet 1").range("addline3).value

outadd = add1 & chr(10) & add2 & chr(10) & add3

worksheets("sheet 1").range("A1").value = outadd

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:

=add1 &"
 " & add2 &"
 " & add3

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:-
> Name, co-name,addline1,addline2,addline3, city, pin, Tel.,
> & E-mail. i have more than 1000 data in it.
> i want to convert all addline1, addline2 & allline3 data
> 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:-
> Name, co-name,addline1,addline2,addline3, city, pin, Tel.,
> & E-mail. i have more than 1000 data in it.
> i want to convert all addline1, addline2 & allline3 data
> into i filed address is it possible to convert all this 3
> cell into 1 cell?
> any help.



Fri, 25 Nov 2005 10:57:28 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Parsing data in one cell into three parts

2. Copying data values from one cell to another, based on other cells values

3. If cell equals one value fill these cells with this data

4. Moving Data From One set of cells to other cells

5. data in one cell -- yes or no in another cell

6. Data from multiple cells into one cell

7. Placing multiple cell data into one single cell

8. many data in one cell distributed to cells across row

9. Concatenate data in two cells into one cell?

10. How do i copy a single cell reference into more than one cell using Data Validat


 
Powered by phpBB® Forum Software © phpBB Group