Google Answers Logo
View Question
 
Q: Excel Question ( Answered,   0 Comments )
Question  
Subject: Excel Question
Category: Computers > Software
Asked by: joel1357-ga
List Price: $20.00
Posted: 21 May 2003 22:49 PDT
Expires: 20 Jun 2003 22:49 PDT
Question ID: 207174
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
Answer  
Subject: Re: Excel Question
Answered By: gwagner-ga on 21 May 2003 22:59 PDT
 
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
Comments  
There are no comments at this time.

Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at answers-support@google.com with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  


Google Home - Answers FAQ - Terms of Service - Privacy Policy