View Question
 ```I have a bunch of words in column A of my Excel spreadsheet. There are 1,800 rows with two to four words per row. There is also a space at the front of the 1st word in each row. I need to pull the 1st word from each row over to the left so that there is no space before the 1st word in each row.. I know this could be said more succintly, I'm tired and just need the solution. Anyway, I would also like to be able to remove the spaces between each of the words in a given row as well as the space at the beginning of each row. Finally I have the same word repeated over and over in column b and I would like to merge column a with column b so that I then have several words with no spaces in between them and no space at the beginning of the row. Please help in simple English. I will tip \$20 if I get the answer in 20 minutes or less from the time the question is posted and I will subtract \$1 from the tip for each minute I have to wait beyond 20 minutes. In other words give me the answer in 20 minutes and you get \$40..give it to me in 30 minutes and you get \$30..get it to me in 40 minutes or more..you get the posted price of the question without a tip. The reason for this is that I have someone in my office that can solve this problem for me in the morning. I don't want to wait until the morning so I'm willing to pay. However the longer I have to wait the less sleep I get which is what I'm really looking for here...the answer quickly so that I can finish my work and go to bed. Thanks, Joel```
 ```Hi Joel, Here is the code for what you'd want to do. Put this in Column C and it'll remove all spaces from Column A and merge Column A & B: =CONCATENATE(SUBSTITUTE(\$A1," ",""),SUBSTITUTE(\$B1," ","")) Once you copy it down 1800 times you'll get your condensed text in the respective rows of column C. Hope that helps. I know how frustrating this can be sometimes. If you have any more questions. Please post a question clarification request and I'll try my best to answer it ASAP. (I'll be online for another 30 min or so tonight.) Best wishes, gwagner-ga``` Clarification of Answer by gwagner-ga on 21 May 2003 23:04 PDT ```In my effort to get this to you ASAP, I forgot to include one qualifyer. I just did this in Excel v.X for Mac. In case your version of Excel doesn't understand these commands, please let me know which version you have and I'll try to figure it out for your version. Additionally, if you only want to do step #1 alone (i.e. remove the leading space in column A), use this command: =TRIM(A1) That will remove the leading space, but not the spaces between words. For that, you'll have to use the "SUBSTITUTE(A1," ","")" command I mentioned before. gwagner-ga``` Request for Answer Clarification by joel1357-ga on 21 May 2003 23:27 PDT ```Okay...it does pull them together..but when I put in the code it takes the formula and turns each new word (combined a and b) and turns it into a double space...also the space is not removed from the left hand side...I also used the other code just for removing the space..I think your on the right track..please help...I got your answer within 11 minutes..I am still interested in giving the tip..just help me through it..thanks..joel``` Clarification of Answer by gwagner-ga on 21 May 2003 23:35 PDT ```Hm, this is strange. Especially the part with turning the words into double spaces. Which version of Excel are you using? Apparently, your version does have these two functions, but the SUBSTITUTE function might work a bit differently. What happens when you use the SUBSTITUTE command alone? i.e. try putting =SUBSTITUTE(A1," ","") in cell C1. What is the result?``` Request for Answer Clarification by joel1357-ga on 21 May 2003 23:42 PDT ```ok...I have it all together..no double spaces..one new word all in column C...now how do I get rid of that space on the left hand side of column C...do I do something in column d ???......Excel 2000``` Clarification of Answer by gwagner-ga on 21 May 2003 23:48 PDT ```Great! Yes, you can get rid of the left-hand space in column C by inserting the following formula in column D: =TRIM(C1) However, if you have followed my previous formula exactly, I don't think you'll have a leading space in column C. It might appear that way in the display, but once you click on it to edit the cell, you should see that there is no leading space. That said, it could very well be that I'm wrong and that there is indeed a leading space. In that case, use the TRIM command above. I suspect you'll be using this info to search for domain names -- judging from your other question I just answered. If that's the case, you'll need to export these names now into a plain text file before copying and pasting them into the edit box at http://www.cybersolution.biz/cgi-bin/zaygo.cgi Good luck, gwagner-ga``` Request for Answer Clarification by joel1357-ga on 21 May 2003 23:54 PDT ```How can I put that trim command in column C since I already have the entire column filled with the new one word column from a and b?``` Clarification of Answer by gwagner-ga on 21 May 2003 23:57 PDT ```Instead of "=CONCATENATE(SUBSTITUTE(\$A1," ",""),SUBSTITUTE(\$B1," ",""))", the formula you now have in column C, put in the following: =TRIM(CONCATENATE(SUBSTITUTE(\$A1," ",""),SUBSTITUTE(\$B1," ",""))) That will do it. Although it would be very strange indeed if you really had this leading space in column C now. Most likely it only appears that way in the cell display of Excel. Either way though, this formula above should do the trick. gwagner-ga``` Request for Answer Clarification by joel1357-ga on 22 May 2003 00:15 PDT ```I'm almost out of steam...to heck with column b...i've eliminated it..how can I get rid of the space in front of the 1st word in column A``` Clarification of Answer by gwagner-ga on 22 May 2003 00:16 PDT ```If you just want to get rid of the space in front of the first word in column A (and keep all the other spaces), here's your formula: =TRIM(A1)``` Request for Answer Clarification by joel1357-ga on 22 May 2003 00:18 PDT `where do I put this command?` Clarification of Answer by gwagner-ga on 22 May 2003 00:20 PDT ```In cell D1, for example. Then you'll have cell A1 reappear in D1, just without the leading space.``` Clarification of Answer by gwagner-ga on 22 May 2003 23:58 PDT ```Hi Joel, Have you been able to complete the cell merge the way you intended it to? gwagner-ga```