How do I merge colums with data

[up4ms]

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

 

[Peo Sjoblom]

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

 

[Mary]

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]

Hi Mary
Thanks for the help. I have 17,000. What would the formula look like in this
situation?

 

[Peo Sjoblom]

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]

pEO THANKS

How do I do a space between first and last name?

 

[up4ms]

Peo

That is cool thanks. One more questions. When it comes to a field without
first name it stops.

 

[Peo Sjoblom]

=D2&" "&E2

first in D2 last in E2 just the way I originally posted

--


Regards,


Peo Sjoblom

 

[Peo Sjoblom]

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