Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Merging Columns
I am trying to 'make one' a number of columns...
Essentially I have several thousand rows of data that I need to clean up... What I would like to accomplish is Column A + B = AB Column A Data: I need this one first Column B Data: and this one comes right behind with a space for proper grammer To equal one column: I need this one first and this one comes right behind with a space for proper grammer. Thanks in advance for any input! Ti |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Merging Columns
Hi
In C1 =A1&" "&B1 Copy down as far as required. Copy column CPastes Special back over the formulae to "fix" the values. -- Regards Roger Govier "Titanium" wrote in message ... I am trying to 'make one' a number of columns... Essentially I have several thousand rows of data that I need to clean up... What I would like to accomplish is Column A + B = AB Column A Data: I need this one first Column B Data: and this one comes right behind with a space for proper grammer To equal one column: I need this one first and this one comes right behind with a space for proper grammer. Thanks in advance for any input! Ti |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Merging Columns
In C1 enter =A1 & " " & B1
Double-click on fill handle of C1 to copy down. Gord Dibben MS Excel MVP On Sun, 29 Jul 2007 08:06:02 -0700, Titanium wrote: I am trying to 'make one' a number of columns... Essentially I have several thousand rows of data that I need to clean up... What I would like to accomplish is Column A + B = AB Column A Data: I need this one first Column B Data: and this one comes right behind with a space for proper grammer To equal one column: I need this one first and this one comes right behind with a space for proper grammer. Thanks in advance for any input! Ti |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Merging Columns
This formula does work. However, it uses up two rows...
It merges 1/2 the data appropriately, but fails to merge every other line... "Roger Govier" wrote: Hi In C1 =A1&" "&B1 Copy down as far as required. Copy column CPastes Special back over the formulae to "fix" the values. -- Regards Roger Govier "Titanium" wrote in message ... I am trying to 'make one' a number of columns... Essentially I have several thousand rows of data that I need to clean up... What I would like to accomplish is Column A + B = AB Column A Data: I need this one first Column B Data: and this one comes right behind with a space for proper grammer To equal one column: I need this one first and this one comes right behind with a space for proper grammer. Thanks in advance for any input! Ti |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Merging Columns
How so unless your data is not as you originally described it?
A1 = first B1 = last A2 = next first B2 = next last In C1 using the formula given you will get "first last" In C2 you will get "nextfirst next last" Gord On Sun, 29 Jul 2007 08:56:01 -0700, Titanium wrote: This formula does work. However, it uses up two rows... It merges 1/2 the data appropriately, but fails to merge every other line... "Roger Govier" wrote: Hi In C1 =A1&" "&B1 Copy down as far as required. Copy column CPastes Special back over the formulae to "fix" the values. -- Regards Roger Govier "Titanium" wrote in message ... I am trying to 'make one' a number of columns... Essentially I have several thousand rows of data that I need to clean up... What I would like to accomplish is Column A + B = AB Column A Data: I need this one first Column B Data: and this one comes right behind with a space for proper grammer To equal one column: I need this one first and this one comes right behind with a space for proper grammer. Thanks in advance for any input! Ti |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Merging Columns
Gord, I could be wrong, but I believe the reason Roger's formula didn't work
for me is because of the spacing. Your formula worked perfectly fine. I do appreciate the help of both Roger and yourself. You saved me countless hours of hellish copy & pasting monotony... :) "Gord Dibben" wrote: In C1 enter =A1 & " " & B1 Double-click on fill handle of C1 to copy down. Gord Dibben MS Excel MVP On Sun, 29 Jul 2007 08:06:02 -0700, Titanium wrote: I am trying to 'make one' a number of columns... Essentially I have several thousand rows of data that I need to clean up... What I would like to accomplish is Column A + B = AB Column A Data: I need this one first Column B Data: and this one comes right behind with a space for proper grammer To equal one column: I need this one first and this one comes right behind with a space for proper grammer. Thanks in advance for any input! Ti |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Merging Columns
Our formulas were basically the same.
Mine had a couple of extra spaces for clarity only. =A1 & " " & B1 Roger left them out. =A1&" "&B1 No difference in function. Gord On Sun, 29 Jul 2007 10:00:01 -0700, Titanium wrote: Gord, I could be wrong, but I believe the reason Roger's formula didn't work for me is because of the spacing. Your formula worked perfectly fine. I do appreciate the help of both Roger and yourself. You saved me countless hours of hellish copy & pasting monotony... :) "Gord Dibben" wrote: In C1 enter =A1 & " " & B1 Double-click on fill handle of C1 to copy down. Gord Dibben MS Excel MVP On Sun, 29 Jul 2007 08:06:02 -0700, Titanium wrote: I am trying to 'make one' a number of columns... Essentially I have several thousand rows of data that I need to clean up... What I would like to accomplish is Column A + B = AB Column A Data: I need this one first Column B Data: and this one comes right behind with a space for proper grammer To equal one column: I need this one first and this one comes right behind with a space for proper grammer. Thanks in advance for any input! Ti |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Merging Columns
Not that I'm questioning the formula, or putting Roger down...
But apparently the spaces do have some effect on the end result... As I've said for many years computers and their functions boil down to two things... 1's for on, true or working... and 0's for off, false and not working... I appreciate all the help from everyone who offered a hand... "Gord Dibben" wrote: Our formulas were basically the same. Mine had a couple of extra spaces for clarity only. =A1 & " " & B1 Roger left them out. =A1&" "&B1 No difference in function. Gord On Sun, 29 Jul 2007 10:00:01 -0700, Titanium wrote: Gord, I could be wrong, but I believe the reason Roger's formula didn't work for me is because of the spacing. Your formula worked perfectly fine. I do appreciate the help of both Roger and yourself. You saved me countless hours of hellish copy & pasting monotony... :) "Gord Dibben" wrote: In C1 enter =A1 & " " & B1 Double-click on fill handle of C1 to copy down. Gord Dibben MS Excel MVP On Sun, 29 Jul 2007 08:06:02 -0700, Titanium wrote: I am trying to 'make one' a number of columns... Essentially I have several thousand rows of data that I need to clean up... What I would like to accomplish is Column A + B = AB Column A Data: I need this one first Column B Data: and this one comes right behind with a space for proper grammer To equal one column: I need this one first and this one comes right behind with a space for proper grammer. Thanks in advance for any input! Ti |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Merging Columns
Hi
Not that I am offended in any way, but as Gord says his formula and mine are identical, other than the way Gord typed his for clarity. Both will work in an identical manner - they cannot do otherwise. -- Regards Roger Govier "Titanium" wrote in message ... Not that I'm questioning the formula, or putting Roger down... But apparently the spaces do have some effect on the end result... As I've said for many years computers and their functions boil down to two things... 1's for on, true or working... and 0's for off, false and not working... I appreciate all the help from everyone who offered a hand... "Gord Dibben" wrote: Our formulas were basically the same. Mine had a couple of extra spaces for clarity only. =A1 & " " & B1 Roger left them out. =A1&" "&B1 No difference in function. Gord On Sun, 29 Jul 2007 10:00:01 -0700, Titanium wrote: Gord, I could be wrong, but I believe the reason Roger's formula didn't work for me is because of the spacing. Your formula worked perfectly fine. I do appreciate the help of both Roger and yourself. You saved me countless hours of hellish copy & pasting monotony... :) "Gord Dibben" wrote: In C1 enter =A1 & " " & B1 Double-click on fill handle of C1 to copy down. Gord Dibben MS Excel MVP On Sun, 29 Jul 2007 08:06:02 -0700, Titanium wrote: I am trying to 'make one' a number of columns... Essentially I have several thousand rows of data that I need to clean up... What I would like to accomplish is Column A + B = AB Column A Data: I need this one first Column B Data: and this one comes right behind with a space for proper grammer To equal one column: I need this one first and this one comes right behind with a space for proper grammer. Thanks in advance for any input! Ti |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Merging Columns
I will certainly admit when I'm wrong... and I am...
I'm really baffled as to what I did... but after retesting the formula it did work. What orginally happened was that it merged every other row...and left one row blank... The results didn't make the slighest bit of sense, but I had to go with what I saw... Again, thanks for your help. "Roger Govier" wrote: Hi Not that I am offended in any way, but as Gord says his formula and mine are identical, other than the way Gord typed his for clarity. Both will work in an identical manner - they cannot do otherwise. -- Regards Roger Govier "Titanium" wrote in message ... Not that I'm questioning the formula, or putting Roger down... But apparently the spaces do have some effect on the end result... As I've said for many years computers and their functions boil down to two things... 1's for on, true or working... and 0's for off, false and not working... I appreciate all the help from everyone who offered a hand... "Gord Dibben" wrote: Our formulas were basically the same. Mine had a couple of extra spaces for clarity only. =A1 & " " & B1 Roger left them out. =A1&" "&B1 No difference in function. Gord On Sun, 29 Jul 2007 10:00:01 -0700, Titanium wrote: Gord, I could be wrong, but I believe the reason Roger's formula didn't work for me is because of the spacing. Your formula worked perfectly fine. I do appreciate the help of both Roger and yourself. You saved me countless hours of hellish copy & pasting monotony... :) "Gord Dibben" wrote: In C1 enter =A1 & " " & B1 Double-click on fill handle of C1 to copy down. Gord Dibben MS Excel MVP On Sun, 29 Jul 2007 08:06:02 -0700, Titanium wrote: I am trying to 'make one' a number of columns... Essentially I have several thousand rows of data that I need to clean up... What I would like to accomplish is Column A + B = AB Column A Data: I need this one first Column B Data: and this one comes right behind with a space for proper grammer To equal one column: I need this one first and this one comes right behind with a space for proper grammer. Thanks in advance for any input! Ti |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Merging Columns
I was having this same problem...However the two columns I am trying to merge
have a date and the time. So when I merge the two columns I am getting the number form of the date in return. I have tried changing the format to show the date and the time but its still returning the number? What should I do to fix this problem? B2=7/23/07 C2=0:00:16 What I want in D2 is: 7/23/07 0:00:16 What I am getting is: 39286 0.00185185.... "Titanium" wrote: I am trying to 'make one' a number of columns... Essentially I have several thousand rows of data that I need to clean up... What I would like to accomplish is Column A + B = AB Column A Data: I need this one first Column B Data: and this one comes right behind with a space for proper grammer To equal one column: I need this one first and this one comes right behind with a space for proper grammer. Thanks in advance for any input! Ti |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Merging Columns
In your situation, don't try to do a text concatenation.
Just use =B2+C2 and format the cell appropriately, such as: mm/dd/yyyy hh:mm:ss -- David Biddulph "Steve B" wrote in message ... I was having this same problem...However the two columns I am trying to merge have a date and the time. So when I merge the two columns I am getting the number form of the date in return. I have tried changing the format to show the date and the time but its still returning the number? What should I do to fix this problem? B2=7/23/07 C2=0:00:16 What I want in D2 is: 7/23/07 0:00:16 What I am getting is: 39286 0.00185185.... "Titanium" wrote: I am trying to 'make one' a number of columns... Essentially I have several thousand rows of data that I need to clean up... What I would like to accomplish is Column A + B = AB Column A Data: I need this one first Column B Data: and this one comes right behind with a space for proper grammer To equal one column: I need this one first and this one comes right behind with a space for proper grammer. Thanks in advance for any input! Ti |
#13
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Merging Columns
Hi was trying to merge two colums in to one, and both columns have about 500
client lists. In column A I have first name and in Column B I had Clients last name. But I wanted to do both in on columns. So instead of doing one at a time I created the following formula in C 1 =CONCATENATE(Sheet1!$A:$A&" "&Sheet1!$B:$B) .... then I select my Formula in C1, then select all the way to client nuber 500 (C500), that means I have highlighted the column C1 to C500, then click Fill, then click down. That makes the same exact formula from C1 to C500. then enter. That will merge all the values in column A and column B. Note: pls note in the formula there two same words Sheet1. That is the sheet you are working on. Normally when we open excel we have Sheet1, Sheet2 and Sheet3 at the bottom of the worksheet we are currently working. So If you already renamed it pls use your name instead of Sheet1, or Sheet2 if you are working on sheet 2. -- www.wilshiretrans.com Limos, Taxis, Shuttles, Stretches, and Buses. "David Biddulph" wrote: In your situation, don't try to do a text concatenation. Just use =B2+C2 and format the cell appropriately, such as: mm/dd/yyyy hh:mm:ss -- David Biddulph "Steve B" wrote in message ... I was having this same problem...However the two columns I am trying to merge have a date and the time. So when I merge the two columns I am getting the number form of the date in return. I have tried changing the format to show the date and the time but its still returning the number? What should I do to fix this problem? B2=7/23/07 C2=0:00:16 What I want in D2 is: 7/23/07 0:00:16 What I am getting is: 39286 0.00185185.... "Titanium" wrote: I am trying to 'make one' a number of columns... Essentially I have several thousand rows of data that I need to clean up... What I would like to accomplish is Column A + B = AB Column A Data: I need this one first Column B Data: and this one comes right behind with a space for proper grammer To equal one column: I need this one first and this one comes right behind with a space for proper grammer. Thanks in advance for any input! Ti |
#14
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Merging Columns
thank you
"Titanium" wrote: Gord, I could be wrong, but I believe the reason Roger's formula didn't work for me is because of the spacing. Your formula worked perfectly fine. I do appreciate the help of both Roger and yourself. You saved me countless hours of hellish copy & pasting monotony... :) "Gord Dibben" wrote: In C1 enter =A1 & " " & B1 Double-click on fill handle of C1 to copy down. Gord Dibben MS Excel MVP On Sun, 29 Jul 2007 08:06:02 -0700, Titanium wrote: I am trying to 'make one' a number of columns... Essentially I have several thousand rows of data that I need to clean up... What I would like to accomplish is Column A + B = AB Column A Data: I need this one first Column B Data: and this one comes right behind with a space for proper grammer To equal one column: I need this one first and this one comes right behind with a space for proper grammer. Thanks in advance for any input! Ti |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
merging columns | New Users to Excel | |||
Merging Columns | Excel Worksheet Functions | |||
Merging columns | New Users to Excel | |||
merging text columns | Excel Discussion (Misc queries) | |||
Merging Columns | Excel Worksheet Functions |