Excel, getting values form two cells so both values show in one cell

chuc
#1 / 8

getting values form two cells so both values show in one cell

is it possible to have two or more cell values appear in one cell
i.e
cell a1 = dog
cell b2 = cat

so the end result would be cell c2 = dog cat

chuck

Wed, 06 Apr 2005 12:05:22 GMT

J.E. McGimpse
#2 / 8

getting values form two cells so both values show in one cell

One way:

C2: =A1 & " " & B2

Quote:

> is it possible to have two or more cell values appear in one cell
> i.e
> cell a1 = dog
> cell b2 = cat

> so the end result would be cell c2 = dog cat

> chuck

Wed, 06 Apr 2005 12:48:18 GMT

Niek Otte
#3 / 8

getting values form two cells so both values show in one cell

=A1&B1

or, with a space in-between,

=A1&" "&B1

--
Regards,

Niek Otten
Microsoft MVP - Excel

Quote:

> is it possible to have two or more cell values appear in one cell
> i.e
> cell a1 = dog
> cell b2 = cat

> so the end result would be cell c2 = dog cat

> chuck

Wed, 06 Apr 2005 12:49:46 GMT

Ron de Brui
#4 / 8

getting values form two cells so both values show in one cell

I want to add this to J.E and Niek solution

maybe nice to if you want to add a lot of cells
put the function in a module

Function MYCONCATONATOR(table As Range, delimiter As String)
For Each cell In table
MYCONCATONATOR = MYCONCATONATOR & cell & delimiter
Next
MYCONCATONATOR = Left(MYCONCATONATOR, Len(MYCONCATONATOR) - 1)
End Function

'and call it like a regular one:

'=MYCONCATONATOR(A1:A3,"?")

'=MYCONCATONATOR(A1:A10," / ")

--
Regards Ron
www.rondebruin.nl/Google.htm

Quote:

> is it possible to have two or more cell values appear in one cell
> i.e
> cell a1 = dog
> cell b2 = cat

> so the end result would be cell c2 = dog cat

> chuck

Wed, 06 Apr 2005 13:04:54 GMT

chuc
#5 / 8

getting values form two cells so both values show in one cell

Thanks to all 3 off you works a treat

Chuck

Quote:

> I want to add this to J.E and Niek solution

> maybe nice to if you want to add a lot of cells
> put the function in a module

> Function MYCONCATONATOR(table As Range, delimiter As String)
> For Each cell In table
> MYCONCATONATOR = MYCONCATONATOR & cell & delimiter
> Next
> MYCONCATONATOR = Left(MYCONCATONATOR, Len(MYCONCATONATOR) - 1)
> End Function

> 'and call it like a regular one:

> '=MYCONCATONATOR(A1:A3,"?")

> '=MYCONCATONATOR(A1:A10," / ")

> --
> Regards Ron
> www.rondebruin.nl/Google.htm

> > is it possible to have two or more cell values appear in one cell
> > i.e
> > cell a1 = dog
> > cell b2 = cat

> > so the end result would be cell c2 = dog cat

> > chuck

Wed, 06 Apr 2005 17:27:06 GMT

phobo
#6 / 8

getting values form two cells so both values show in one cell

Try:

=A1 & " " & B1

Quote:

>is it possible to have two or more cell values appear in one cell
>i.e
>cell a1 = dog
>cell b2 = cat

>so the end result would be cell c2 = dog cat

>chuck

--
Greg

http://houston.quik.com/phobos

Wed, 06 Apr 2005 18:00:49 GMT

J.E. McGimpse
#7 / 8

getting values form two cells so both values show in one cell

Ron -

I've been using a similar routine for a while - I add an optional
argument for direction:

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

Quote:

> I want to add this to J.E and Niek solution

> maybe nice to if you want to add a lot of cells
> put the function in a module

> Function MYCONCATONATOR(table As Range, delimiter As String)
> For Each cell In table
> MYCONCATONATOR = MYCONCATONATOR & cell & delimiter
> Next
> MYCONCATONATOR = Left(MYCONCATONATOR, Len(MYCONCATONATOR) - 1)
> End Function

> 'and call it like a regular one:

> '=MYCONCATONATOR(A1:A3,"?")

> '=MYCONCATONATOR(A1:A10," / ")

Wed, 06 Apr 2005 19:24:46 GMT

Ron de Brui
#8 / 8

getting values form two cells so both values show in one cell

Thanks J.E

For posting it

--
Regards Ron
www.rondebruin.nl/Google.htm

Quote:

> Ron -

> I've been using a similar routine for a while - I add an optional
> argument for direction:

> 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

> > I want to add this to J.E and Niek solution

> > maybe nice to if you want to add a lot of cells
> > put the function in a module

> > Function MYCONCATONATOR(table As Range, delimiter As String)
> > For Each cell In table
> > MYCONCATONATOR = MYCONCATONATOR & cell & delimiter
> > Next
> > MYCONCATONATOR = Left(MYCONCATONATOR, Len(MYCONCATONATOR) - 1)
> > End Function

> > 'and call it like a regular one:

> > '=MYCONCATONATOR(A1:A3,"?")

> > '=MYCONCATONATOR(A1:A10," / ")

Wed, 06 Apr 2005 19:47:33 GMT