Inserting data that has different column headings

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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!
 
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!
 
--
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
 
Back
Top