| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Multiple cells reference
Let's suppose the following facts:
I have two cells, each one containing one number. I want to make a refference of the two values that I need to view in a third cell, separated by commas or ";" or anything else. So, if two cells have the "44" and "55" vallues, can I have a third cell that will contain the following value: "44,55" or "44;55" or "44 55"? Please help me if there's any chance. Thanks. |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple cells reference
If 44 was in A1 and 55 was in B1 you could use:
=A1&","&B1 and it will give you 44,55 (Just change what is between the quotation marks of you want a semi colon or a space instead of the comma.) "Elis" wrote: Let's suppose the following facts: I have two cells, each one containing one number. I want to make a refference of the two values that I need to view in a third cell, separated by commas or ";" or anything else. So, if two cells have the "44" and "55" vallues, can I have a third cell that will contain the following value: "44,55" or "44;55" or "44 55"? Please help me if there's any chance. Thanks. |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple cells reference
thank you!
now, is it possible for a range of cells? (A2 to A20) "tim m" wrote: If 44 was in A1 and 55 was in B1 you could use: =A1&","&B1 and it will give you 44,55 (Just change what is between the quotation marks of you want a semi colon or a space instead of the comma.) "Elis" wrote: Let's suppose the following facts: I have two cells, each one containing one number. I want to make a refference of the two values that I need to view in a third cell, separated by commas or ";" or anything else. So, if two cells have the "44" and "55" vallues, can I have a third cell that will contain the following value: "44,55" or "44;55" or "44 55"? Please help me if there's any chance. Thanks. |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple cells reference
Drag the Fill Handle from C1 to C20
"Elis" wrote: thank you! now, is it possible for a range of cells? (A2 to A20) "tim m" wrote: If 44 was in A1 and 55 was in B1 you could use: =A1&","&B1 and it will give you 44,55 (Just change what is between the quotation marks of you want a semi colon or a space instead of the comma.) "Elis" wrote: Let's suppose the following facts: I have two cells, each one containing one number. I want to make a refference of the two values that I need to view in a third cell, separated by commas or ";" or anything else. So, if two cells have the "44" and "55" vallues, can I have a third cell that will contain the following value: "44,55" or "44;55" or "44 55"? Please help me if there's any chance. Thanks. |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple cells reference
Not that... I meant to list the values from multiple cells in a single cell.
Something like =A1&","&A2&","&A3&","&A4&","&A5&","&A6& etc but for a looooong list of values. So, is there a short mode? "Teethless mama" wrote: Drag the Fill Handle from C1 to C20 "Elis" wrote: thank you! now, is it possible for a range of cells? (A2 to A20) "tim m" wrote: If 44 was in A1 and 55 was in B1 you could use: =A1&","&B1 and it will give you 44,55 (Just change what is between the quotation marks of you want a semi colon or a space instead of the comma.) "Elis" wrote: Let's suppose the following facts: I have two cells, each one containing one number. I want to make a refference of the two values that I need to view in a third cell, separated by commas or ";" or anything else. So, if two cells have the "44" and "55" vallues, can I have a third cell that will contain the following value: "44,55" or "44;55" or "44 55"? Please help me if there's any chance. Thanks. |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple cells reference
Elis
No shorthand method without some VBA. Function ConCatRange(CellBlock As Range) As String Dim cell As Range Dim sbuf As String For Each cell In CellBlock If Len(cell.text) 0 Then sbuf = sbuf & cell.text & "," 'change the comma(",") to your choice of separator Next ConCatRange = Left(sbuf, Len(sbuf) - 1) End Function Usage is: =ConCatRange(A1:A20) If not familiar with VBA and macros, see David McRitchie's site for more on "getting started". http://www.mvps.org/dmcritchie/excel/getstarted.htm In the meantime.......... To create a General Module, hit ALT + F11 to open the Visual Basic Editor. Hit CRTL + R to open Project Explorer. Find your workbook/project and select it. Right-click and InsertModule. Paste the code in there. Save the workbook and hit ALT + Q to return to Excel window. Enter the formula in a helper cell as explained above. Gord Dibben MS Excel MVP On Mon, 2 Apr 2007 11:16:03 -0700, Elis wrote: Not that... I meant to list the values from multiple cells in a single cell. Something like =A1&","&A2&","&A3&","&A4&","&A5&","&A6& etc but for a looooong list of values. So, is there a short mode? "Teethless mama" wrote: Drag the Fill Handle from C1 to C20 "Elis" wrote: thank you! now, is it possible for a range of cells? (A2 to A20) "tim m" wrote: If 44 was in A1 and 55 was in B1 you could use: =A1&","&B1 and it will give you 44,55 (Just change what is between the quotation marks of you want a semi colon or a space instead of the comma.) "Elis" wrote: Let's suppose the following facts: I have two cells, each one containing one number. I want to make a refference of the two values that I need to view in a third cell, separated by commas or ";" or anything else. So, if two cells have the "44" and "55" vallues, can I have a third cell that will contain the following value: "44,55" or "44;55" or "44 55"? Please help me if there's any chance. Thanks. |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple cells reference
Yes.
-- David Biddulph "Elis" wrote in message ... thank you! now, is it possible for a range of cells? (A2 to A20) "tim m" wrote: If 44 was in A1 and 55 was in B1 you could use: =A1&","&B1 and it will give you 44,55 (Just change what is between the quotation marks of you want a semi colon or a space instead of the comma.) "Elis" wrote: Let's suppose the following facts: I have two cells, each one containing one number. I want to make a refference of the two values that I need to view in a third cell, separated by commas or ";" or anything else. So, if two cells have the "44" and "55" vallues, can I have a third cell that will contain the following value: "44,55" or "44;55" or "44 55"? Please help me if there's any chance. Thanks. |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Multiple cells reference
=A1&","&B1
=A1&";"&B1 =A1&" "&B1 -- David Biddulph "Elis" wrote in message ... Let's suppose the following facts: I have two cells, each one containing one number. I want to make a refference of the two values that I need to view in a third cell, separated by commas or ";" or anything else. So, if two cells have the "44" and "55" vallues, can I have a third cell that will contain the following value: "44,55" or "44;55" or "44 55"? Please help me if there's any chance. Thanks. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Formulas that reference cells that reference another cell | Excel Discussion (Misc queries) | |||
| Replacing sheet reference in multiple cells... | Excel Worksheet Functions | |||
| Populate multiple cells using the same reference | Excel Worksheet Functions | |||
| Reference multiple cells in if statement | Excel Worksheet Functions | |||
| changing multiple cells from relative to absolute reference | Excel Discussion (Misc queries) | |||
Hybrid Mode
