How do I move this data?

  • Thread starter Thread starter Paul
  • Start date Start date
P

Paul

I have some excel data that I need to merge into one cell... for
example

ADDR1-ST Num ADDR1-ST Name
111 E MAIN ST


I want to put it all in one cell
ADDR1-ST Name
111 E MAIN ST

I have about 15,000 records so I want the [SPACE] between the Number
and the Name

any ideas?
thanks.
 
If the first address is in A2:B2 then this should work =A2&" "&B2
But your data as some hard-spaces after the 111 (this could have resulted
from the email) so you may need to use =SUBSTITUTE(A2,CHAR(160),"")&" "&B2


When you have this working you could copy the new data and with it still
selected use Edit | Paste Special with Multiply checked. Then the new data
is free of the old
best wishes
 
Actually, that was from the email...
111 is in C2, C3, C4....etc
E MAIN ST is D2, D3, D4...etc

And where do I put this code to do this? in a new column?

If the first address is in A2:B2 then this should work =A2&" "&B2
But your data as some hard-spaces after the 111 (this could have resulted
from the email) so you may need to use =SUBSTITUTE(A2,CHAR(160),"")&" "&B2

When you have this working you could copy the new data and with it still
selected use Edit | Paste Special with Multiply checked. Then the new data
is free of the old
best wishes
--
Bernard V Liengme
Microsoft Excel MVPhttp://people.stfx.ca/bliengme
remove caps from email


I have some excel data that I need to merge into one cell... for
example
Click to expand...
ADDR1-ST Num     ADDR1-ST Name
111                       E MAIN ST
Click to expand...
I want to put it all in one cell
ADDR1-ST Name
111 E MAIN ST
Click to expand...
I have about 15,000 records so I want the [SPACE] between the Number
and the Name
Click to expand...
any ideas?
thanks.
Click to expand...
Click to expand...
 
Put the formula in the cell where you want the result; say F14
The copy it down the column to get all the other addresses
best wishes
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

Actually, that was from the email...
111 is in C2, C3, C4....etc
E MAIN ST is D2, D3, D4...etc

And where do I put this code to do this? in a new column?

If the first address is in A2:B2 then this should work =A2&" "&B2
But your data as some hard-spaces after the 111 (this could have resulted
from the email) so you may need to use =SUBSTITUTE(A2,CHAR(160),"")&" "&B2

When you have this working you could copy the new data and with it still
selected use Edit | Paste Special with Multiply checked. Then the new data
is free of the old
best wishes
--
Bernard V Liengme
Microsoft Excel MVPhttp://people.stfx.ca/bliengme
remove caps from email


I have some excel data that I need to merge into one cell... for
example
Click to expand...
ADDR1-ST Num ADDR1-ST Name
111 E MAIN ST
Click to expand...
I want to put it all in one cell
ADDR1-ST Name
111 E MAIN ST
Click to expand...
I have about 15,000 records so I want the [SPACE] between the Number
and the Name
Click to expand...
any ideas?
thanks.
Click to expand...
Click to expand...
 
Back
Top