Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I merge colums with data
I have imported data to excel. The problem is the phone number is broke into
three separate colums. Also the customer first name and customer last name is broke into two separate colums. I need to mergethe three phone number colums into oneand also merge the first and last name into one. Can someone help me to figure this one out? Thanks Loren |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I merge colums with data
Phone numbers in A2, B2 and C2, first name in D2 and Last name in E2
=--(A2&B2&C2) format special and phone number =D2&" "&E2 once you are done copy and paste special and select values to make them independent of the originals -- Regards, Peo Sjoblom "up4ms" wrote in message ... I have imported data to excel. The problem is the phone number is broke into three separate colums. Also the customer first name and customer last name is broke into two separate colums. I need to mergethe three phone number colums into oneand also merge the first and last name into one. Can someone help me to figure this one out? Thanks Loren |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I merge colums with data
Hi UP4MS - you need to use the CONCATENATE function.
PHONE - if you have 425-555-1212 in separate columns (425 in cell A1, 555 in cell B1, and 1212 in cell C1), use =CONCATENATE(A1,"-",B1,"-",C1) in cell D1 to get 425-555-1212. Eliminate the "-" if you don't want the dashes in the results. Use the same principle to create a name field, where "Joe" in cell A1 and "Smith" in cell B1 becomes "Joe Smith" by using =CONCATENATE(A1,"-",B1) in cell C1. If you need to rid yourself of the formulas later, just copy and paste the results as text. "up4ms" wrote: I have imported data to excel. The problem is the phone number is broke into three separate colums. Also the customer first name and customer last name is broke into two separate colums. I need to mergethe three phone number colums into oneand also merge the first and last name into one. Can someone help me to figure this one out? Thanks Loren |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I merge colums with data
Hi Mary
Thanks for the help. I have 17,000. What would the formula look like in this situation? "Mary" wrote: Hi UP4MS - you need to use the CONCATENATE function. PHONE - if you have 425-555-1212 in separate columns (425 in cell A1, 555 in cell B1, and 1212 in cell C1), use =CONCATENATE(A1,"-",B1,"-",C1) in cell D1 to get 425-555-1212. Eliminate the "-" if you don't want the dashes in the results. Use the same principle to create a name field, where "Joe" in cell A1 and "Smith" in cell B1 becomes "Joe Smith" by using =CONCATENATE(A1,"-",B1) in cell C1. If you need to rid yourself of the formulas later, just copy and paste the results as text. "up4ms" wrote: I have imported data to excel. The problem is the phone number is broke into three separate colums. Also the customer first name and customer last name is broke into two separate colums. I need to mergethe three phone number colums into oneand also merge the first and last name into one. Can someone help me to figure this one out? Thanks Loren |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I merge colums with data
Why would you even use that at all, just use the ampersand and the unary
minuses to make them real numbers and then format as phone number? However you would need to copy the formula 17,000 times which is not as hard as it might sound. Just make sure the column where you use the formula is adjacent to one of the columns with data, then type the formula in the first cell, press enter. Go back to the cell you just entered and move the cursor to the lower right corner and when the cursor changes from a thick cross to a thin, double click the left mouse button and it will be copied automatically. Nevertheless using CONCATENATE is not necessary, use the ampersand to do the same thing. -- Regards, Peo Sjoblom "up4ms" wrote in message ... Hi Mary Thanks for the help. I have 17,000. What would the formula look like in this situation? "Mary" wrote: Hi UP4MS - you need to use the CONCATENATE function. PHONE - if you have 425-555-1212 in separate columns (425 in cell A1, 555 in cell B1, and 1212 in cell C1), use =CONCATENATE(A1,"-",B1,"-",C1) in cell D1 to get 425-555-1212. Eliminate the "-" if you don't want the dashes in the results. Use the same principle to create a name field, where "Joe" in cell A1 and "Smith" in cell B1 becomes "Joe Smith" by using =CONCATENATE(A1,"-",B1) in cell C1. If you need to rid yourself of the formulas later, just copy and paste the results as text. "up4ms" wrote: I have imported data to excel. The problem is the phone number is broke into three separate colums. Also the customer first name and customer last name is broke into two separate colums. I need to mergethe three phone number colums into oneand also merge the first and last name into one. Can someone help me to figure this one out? Thanks Loren |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I merge colums with data
pEO THANKS
How do I do a space between first and last name? "Peo Sjoblom" wrote: Phone numbers in A2, B2 and C2, first name in D2 and Last name in E2 =--(A2&B2&C2) format special and phone number =D2&" "&E2 once you are done copy and paste special and select values to make them independent of the originals -- Regards, Peo Sjoblom "up4ms" wrote in message ... I have imported data to excel. The problem is the phone number is broke into three separate colums. Also the customer first name and customer last name is broke into two separate colums. I need to mergethe three phone number colums into oneand also merge the first and last name into one. Can someone help me to figure this one out? Thanks Loren |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I merge colums with data
Peo
That is cool thanks. One more questions. When it comes to a field without first name it stops. "Peo Sjoblom" wrote: Why would you even use that at all, just use the ampersand and the unary minuses to make them real numbers and then format as phone number? However you would need to copy the formula 17,000 times which is not as hard as it might sound. Just make sure the column where you use the formula is adjacent to one of the columns with data, then type the formula in the first cell, press enter. Go back to the cell you just entered and move the cursor to the lower right corner and when the cursor changes from a thick cross to a thin, double click the left mouse button and it will be copied automatically. Nevertheless using CONCATENATE is not necessary, use the ampersand to do the same thing. -- Regards, Peo Sjoblom "up4ms" wrote in message ... Hi Mary Thanks for the help. I have 17,000. What would the formula look like in this situation? "Mary" wrote: Hi UP4MS - you need to use the CONCATENATE function. PHONE - if you have 425-555-1212 in separate columns (425 in cell A1, 555 in cell B1, and 1212 in cell C1), use =CONCATENATE(A1,"-",B1,"-",C1) in cell D1 to get 425-555-1212. Eliminate the "-" if you don't want the dashes in the results. Use the same principle to create a name field, where "Joe" in cell A1 and "Smith" in cell B1 becomes "Joe Smith" by using =CONCATENATE(A1,"-",B1) in cell C1. If you need to rid yourself of the formulas later, just copy and paste the results as text. "up4ms" wrote: I have imported data to excel. The problem is the phone number is broke into three separate colums. Also the customer first name and customer last name is broke into two separate colums. I need to mergethe three phone number colums into oneand also merge the first and last name into one. Can someone help me to figure this one out? Thanks Loren |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I merge colums with data
=D2&" "&E2
first in D2 last in E2 just the way I originally posted -- Regards, Peo Sjoblom "up4ms" wrote in message ... pEO THANKS How do I do a space between first and last name? "Peo Sjoblom" wrote: Phone numbers in A2, B2 and C2, first name in D2 and Last name in E2 =--(A2&B2&C2) format special and phone number =D2&" "&E2 once you are done copy and paste special and select values to make them independent of the originals -- Regards, Peo Sjoblom "up4ms" wrote in message ... I have imported data to excel. The problem is the phone number is broke into three separate colums. Also the customer first name and customer last name is broke into two separate colums. I need to mergethe three phone number colums into oneand also merge the first and last name into one. Can someone help me to figure this one out? Thanks Loren |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
How do I merge colums with data
Are there many cells like that, if not you can just grab the lower right
corner of the cell and drag down until you have passed the empty cells or you can do as follows, assume you have =D500&" "&E500 in the cell where it stopped, assume it was cell F500, select F500, go to the namebox where it should say F500 (the name box is above the header of column A). Click there to make sure the cursor is in that box then add a colon : and type F17000 so the namebox should look like F500:F17000 then press enter (which will highlight/select F500:F17000), then press ctrl + d top copy down your formula That will also copy down the formula replace F500:F17000 with your real numbers -- Regards, Peo Sjoblom -- Regards, Peo Sjoblom "up4ms" wrote in message ... Peo That is cool thanks. One more questions. When it comes to a field without first name it stops. "Peo Sjoblom" wrote: Why would you even use that at all, just use the ampersand and the unary minuses to make them real numbers and then format as phone number? However you would need to copy the formula 17,000 times which is not as hard as it might sound. Just make sure the column where you use the formula is adjacent to one of the columns with data, then type the formula in the first cell, press enter. Go back to the cell you just entered and move the cursor to the lower right corner and when the cursor changes from a thick cross to a thin, double click the left mouse button and it will be copied automatically. Nevertheless using CONCATENATE is not necessary, use the ampersand to do the same thing. -- Regards, Peo Sjoblom "up4ms" wrote in message ... Hi Mary Thanks for the help. I have 17,000. What would the formula look like in this situation? "Mary" wrote: Hi UP4MS - you need to use the CONCATENATE function. PHONE - if you have 425-555-1212 in separate columns (425 in cell A1, 555 in cell B1, and 1212 in cell C1), use =CONCATENATE(A1,"-",B1,"-",C1) in cell D1 to get 425-555-1212. Eliminate the "-" if you don't want the dashes in the results. Use the same principle to create a name field, where "Joe" in cell A1 and "Smith" in cell B1 becomes "Joe Smith" by using =CONCATENATE(A1,"-",B1) in cell C1. If you need to rid yourself of the formulas later, just copy and paste the results as text. "up4ms" wrote: I have imported data to excel. The problem is the phone number is broke into three separate colums. Also the customer first name and customer last name is broke into two separate colums. I need to mergethe three phone number colums into oneand also merge the first and last name into one. Can someone help me to figure this one out? Thanks Loren |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
mail merge excludes my headers and critical data in Word merge | Excel Discussion (Misc queries) | |||
how do i get my mail merge to update the data source at each merge | Excel Discussion (Misc queries) | |||
Mail Merge? 5 Colums | Excel Discussion (Misc queries) | |||
How can I hide data in colums. | Excel Discussion (Misc queries) | |||
same data colums | Excel Worksheet Functions |