Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inserting data that has different column headings
I am working on two spreadsheets. The first one is our main database, and I
want to insert data from the second spreadsheet. My problems is that the first spreadsheet has both the first name and surname in one cell under "contact name", whereas the second spreadsheet as the first names and surnames split between separate columns. Is there a way to combine the two columns to put the first names and surnames on the second spread sheet in one column? There are around 590 rows in the second spreadsheet (1,000s) in the first, so I don't really want to enter the names manually! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inserting data that has different column headings
First of all, put the one blank columns between the name and surname. Add a
comma to the empty column. Then, insert one more column before or after the three columns. A B C D NAME "," SURNAME "EMPTY" Then go to column D, select the first cell and go to "Insert" "function" "concatenate" select: in text 1: A in text 2: B in text 3: C When you finish copy drag the first cell until the end. Then select D column and go to "Copy" "paste special" "Values Only" Then select D column and press CTRL and F. A window opens. Go to "Replace" tab and replace the comma (","), with a space. Press replace to all and you are done. Maybe it's long but it's the only way I know. Maybe someone else could help you with macros. Hope it helps! "Hayles98" wrote: I am working on two spreadsheets. The first one is our main database, and I want to insert data from the second spreadsheet. My problems is that the first spreadsheet has both the first name and surname in one cell under "contact name", whereas the second spreadsheet as the first names and surnames split between separate columns. Is there a way to combine the two columns to put the first names and surnames on the second spread sheet in one column? There are around 590 rows in the second spreadsheet (1,000s) in the first, so I don't really want to enter the names manually! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Inserting data that has different column headings
-- Farhad Hodjat just write this formula in the next available column: A B C 1 Name Surname =A1&" "&B1 and then copy drag the first cell until the end Then select C column (if the cells you copied are already selected then you don't need to select the column C) and hit Ctrl+C then right click on the selected range and choos paste special and choos Values and choos OK then you have combined name & surname in column D "ice" wrote: First of all, put the one blank columns between the name and surname. Add a comma to the empty column. Then, insert one more column before or after the three columns. A B C D NAME "," SURNAME "EMPTY" Then go to column D, select the first cell and go to "Insert" "function" "concatenate" select: in text 1: A in text 2: B in text 3: C When you finish copy drag the first cell until the end. Then select D column and go to "Copy" "paste special" "Values Only" Then select D column and press CTRL and F. A window opens. Go to "Replace" tab and replace the comma (","), with a space. Press replace to all and you are done. Maybe it's long but it's the only way I know. Maybe someone else could help you with macros. Hope it helps! "Hayles98" wrote: I am working on two spreadsheets. The first one is our main database, and I want to insert data from the second spreadsheet. My problems is that the first spreadsheet has both the first name and surname in one cell under "contact name", whereas the second spreadsheet as the first names and surnames split between separate columns. Is there a way to combine the two columns to put the first names and surnames on the second spread sheet in one column? There are around 590 rows in the second spreadsheet (1,000s) in the first, so I don't really want to enter the names manually! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create Headings from Column Data | Excel Worksheet Functions | |||
Inputting data to one worksheet for it effect another | Excel Discussion (Misc queries) | |||
Return SEARCHED Column Number of Numeric Label and Value | Excel Worksheet Functions | |||
From several workbooks onto one excel worksheet | Excel Discussion (Misc queries) | |||
Sort pages? | Excel Discussion (Misc queries) |