Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Concatenation
I have inherited a workbook at my job where the original designer created one
column with both first and last name in that order. Using the Data menu I created two columns, one with the first name and one with the last name. While the company wants to have both columns, the also want one column that is last name, a comma, and then first name. I was able to concatenate it (=B2&", "&A2). However the file has 60,000 records and I dont want to drag the fill handle through the 60,000 rows. Is there any way I can do it by creating a function, or any other way you can think of? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Concatenation
Goto the name box (immediately above column A) and type in the range where
you want the formulas to go then hit ENTER. This will select that range with the first cell being the active cell. Type the formula then instead of hitting ENTER hold down the CTRL key then hit ENTER. This will enter the formula in every cell of the selected range. Just make sure you use relative references: =B2&", "&A2 -- Biff Microsoft Excel MVP "Sol" wrote in message ... I have inherited a workbook at my job where the original designer created one column with both first and last name in that order. Using the Data menu I created two columns, one with the first name and one with the last name. While the company wants to have both columns, the also want one column that is last name, a comma, and then first name. I was able to concatenate it (=B2&", "&A2). However the file has 60,000 records and I dont want to drag the fill handle through the 60,000 rows. Is there any way I can do it by creating a function, or any other way you can think of? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Concatenation
If the formula column is adjacent to one of the name columns, just double-click
on the fill handle of the top formula cell. Gord Dibben MS Excel MVP On Sun, 21 Oct 2007 21:43:00 -0700, Sol wrote: I have inherited a workbook at my job where the original designer created one column with both first and last name in that order. Using the Data menu I created two columns, one with the first name and one with the last name. While the company wants to have both columns, the also want one column that is last name, a comma, and then first name. I was able to concatenate it (=B2&", "&A2). However the file has 60,000 records and I dont want to drag the fill handle through the 60,000 rows. Is there any way I can do it by creating a function, or any other way you can think of? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Concatenation
Biff:
I must be doing something wrong. After I select the range using the name box, as soon as I click in C2, the range is no longer selected. Specifically: I have entered c2:c61701 and hit ENTER which highlights all cells in that range. As soon as I click c2, the range disappears from the name box and is replaced by c2. What am I missing? "T. Valko" wrote: Goto the name box (immediately above column A) and type in the range where you want the formulas to go then hit ENTER. This will select that range with the first cell being the active cell. Type the formula then instead of hitting ENTER hold down the CTRL key then hit ENTER. This will enter the formula in every cell of the selected range. Just make sure you use relative references: =B2&", "&A2 -- Biff Microsoft Excel MVP "Sol" wrote in message ... I have inherited a workbook at my job where the original designer created one column with both first and last name in that order. Using the Data menu I created two columns, one with the first name and one with the last name. While the company wants to have both columns, the also want one column that is last name, a comma, and then first name. I was able to concatenate it (=B2&", "&A2). However the file has 60,000 records and I dont want to drag the fill handle through the 60,000 rows. Is there any way I can do it by creating a function, or any other way you can think of? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Concatenation
Hey Gord:
Thanks. It works like a dream. "Gord Dibben" wrote: If the formula column is adjacent to one of the name columns, just double-click on the fill handle of the top formula cell. Gord Dibben MS Excel MVP On Sun, 21 Oct 2007 21:43:00 -0700, Sol wrote: I have inherited a workbook at my job where the original designer created one column with both first and last name in that order. Using the Data menu I created two columns, one with the first name and one with the last name. While the company wants to have both columns, the also want one column that is last name, a comma, and then first name. I was able to concatenate it (=B2&", "&A2). However the file has 60,000 records and I dont want to drag the fill handle through the 60,000 rows. Is there any way I can do it by creating a function, or any other way you can think of? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Concatenation
I have entered c2:c61701 and hit ENTER which highlights
all cells in that range. As soon as I click c2, the range disappears from the name box and is replaced by c2. What am I missing? Don't click in C2. When you type the range in the name box and then hit ENTER C2 is already selected. Just type the forumla and hit CTRL ENTER. Or, use Gord's suggestion, it's better! -- Biff Microsoft Excel MVP "Sol" wrote in message ... Biff: I must be doing something wrong. After I select the range using the name box, as soon as I click in C2, the range is no longer selected. Specifically: I have entered c2:c61701 and hit ENTER which highlights all cells in that range. As soon as I click c2, the range disappears from the name box and is replaced by c2. What am I missing? "T. Valko" wrote: Goto the name box (immediately above column A) and type in the range where you want the formulas to go then hit ENTER. This will select that range with the first cell being the active cell. Type the formula then instead of hitting ENTER hold down the CTRL key then hit ENTER. This will enter the formula in every cell of the selected range. Just make sure you use relative references: =B2&", "&A2 -- Biff Microsoft Excel MVP "Sol" wrote in message ... I have inherited a workbook at my job where the original designer created one column with both first and last name in that order. Using the Data menu I created two columns, one with the first name and one with the last name. While the company wants to have both columns, the also want one column that is last name, a comma, and then first name. I was able to concatenate it (=B2&", "&A2). However the file has 60,000 records and I dont want to drag the fill handle through the 60,000 rows. Is there any way I can do it by creating a function, or any other way you can think of? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
concatenation | Excel Worksheet Functions | |||
Help with Concatenation | Excel Worksheet Functions | |||
Help with Concatenation | Excel Worksheet Functions | |||
Concatenation | Excel Worksheet Functions | |||
Concatenation | Excel Discussion (Misc queries) |