excel copy paste issue - pulling my hair out!!?

Question

I want to copy paste 2 colums into notepad with out creating a space, or I want to know how to grt rid of the space. I am trying to create domain names in bulk in excel.

example:
column1 column2
texas restaurant.com
dallas restaurant.com
california restaurant.com


but when i copy both columns it keeps the two words seperate, how can i make it look like texasrestaurant.com. dallasrestaraunt.com...etc. there are about 20,000 of them so manually fixing it is not an option.


if you know how to remove the space, you are a god among men!!!

Answer 1

The easiest way I know to do this is to combine them in Excel first and then export to your text file. First, you will want to remove any spaces in column A. You can do this in column A if you want or you can copy and paste this info into a new column, say column C. Use Ctrl+H to do this. In the first box (find), type a space only. Leave the second box (replace with) blank. Hit replace all. This will give you the result texasrestaurant from texas restaurant.



To append the domain, enter this formula in cell D1

=C1&"."&B1

Using the & is an easier way of doing CONCATENATE. You can use this to combine the contents of any two cells (number, text, etc...) into one cell. If you want to put any kind of text or special characters - in this case a period - between the two sets of data, place it in between double quotes (") and it will give you the desired output. In this case, texasrestaurant and com will give you a result of texasrestaurant.com



Copy column D and click on Sheet 2 (or another sheet) and use Paste Special to place the data. Select Values from the pop up window and select OK and then hit enter to paste. You can now export this sheet to your text file to get only the email addresses you want.



Alternatively, if all of the domains are .com domains, you can simplify this by using this formula:

=C1&".com" to simply append a .com to your space-removed restaurant name (from column C).



May seem complicated but is actually pretty easy to do. If you need additional tips on merging text, use the help menu in excel and do a search for Combine Text. There is plenty more info and some good examples there.

Answer 2

You might be able to remove the spaces with little hassle just using notepad. Select the space between words and copy (CTRL+C). Then use the replace function (CTRL+H). In the "find what" field you want to paste (CTRL+V) your space. Then leave the "replace with" field blank. Choose "replace all" and you should be done without exel!



If that doesn't work then you need to learn the concatenate function in excel. So if you have items in column a that you want together with column b, then here's what you can do:



CELL a1 has "texas"

CELL b1 has "restaurant.com"



In cell c1 you type "=concatenate(a1,b1)"



Then you can drag that formula to instantly do the same thing for thousands of entries.



If there are still spaces in your entries then you can easily remove them with the "replace" function (CTRL+H):



In the field "Find what:" you type in a SPACE

In the field "Replace with:" you leave blank



Choose the option "Replace All" and your spaces are gone thousands of times!

Answer 3

Combine them first. When dealing with notepad it gets complicated.



Cell 1. Cell 2



Click in Cell 3, click on your functiobs tool bare and choose "condensate", then click cell 1, tab, click cell 2, ok.



Now click and highlight all the way until the end of the data you are combining. Then hit CTRL "D"



Now highlight that info and drop it in to notepad.

Answer 4

(note that in the code under _ is used to continue a application line on the following line. It is going to work as shown in Excel too, however I almost always did it because Y!Answers does wierd stuff to lengthy traces. ) in case you report your Macro then go to tools->Macro->Macros... Then click on the "Edit Macro" button you're going to see something a little like... Sub Macro1() ' ' Macro1 Macro ' Macro recorded 29/02/2008 by using range("A1:E1").Prefer decision.Copy Sheets("Sheet2").Select variety("A1").Pick ActiveSheet.Paste end Sub Your drawback is that the Macro comprises a bit of of code which selects the identical little bit of textual content as you chose first time round (ie the "range("A1:E1").Prefer" line) simply delete that line and the Macro will replica the presently selected knowledge to the brand new sheet. That is, although, a rather inelegant macro, as all recorded ones are usually. There is no ought to prefer cells earlier than pasting. Try this code alternatively. The identical thing, however multi function line. Determination.Copy Sheets("Sheet2").Variety("A1") _ .PasteSpecial the thing is, though, that you usually wish to paste to a different line every time. This variation uses the .End process of the variety objects to emulate the outcome of Ctrl-Arrow keys to find the bottom non-blank line within the sheet and then the .Offset approach to decide upon the line beneath that. Sub Macro1() decision.Reproduction Sheets("Sheet2").Range _ ("A65535").End(xlUp).Offset(1, zero).PasteSpecial end Sub link the macro to a keyboard shortcut and you are sorted. You could link the macro to a right-clck menu, add it as a command button, put it within the menu bar or a toolbar or have it called automatically in any number of approaches. For illustration, in case you go into the visible general editor and insert this into the code area of Sheet1 then any time you opt for a telephone or neighborhood in Sheet 1 the contents will automatically be transferred to Sheet 2. I doubt that you need that, but I point out it as an illustration of utilizing the worksheet and workbook movements to automate duties confidential Sub Worksheet_SelectionChange _ (ByVal target As range) selection.Reproduction Sheets("Sheet2").Range _ ("A65535").Finish(xlUp).Offset(1, zero).PasteSpecial finish Sub i have tried to offer you a flavour right here of what you can do when you begin enhancing macros with the aid of hand. There's nearly no side of Excel that you cant exchange the looks or behaviour of when you get familiar with VBA.