MrExcel.com - Your One Stop for Excel Tips & Solutions

=Left(I1,???),

Posted by Jimmy on September 05, 2001 12:34 PM

IN the formula =Left(1I,???), I would like to move the first word(s) prior to a comma into another cell. I know I can use the Left command, but not for sure if I can make it go all the way to first comma or just a certain number of characters.

IE:

Computer, Compaq 500 MH
Auto Parts, 327 cubic in

What I would like is to be able to move Computer and Auto Parts into a seperate cell, take off the comma and any spaces left before the text that was left in the orginal cell. I hope this makes sense.

Thanks

Posted by Barrie Davidson on September 05, 2001 12:38 PM

Try
=LEFT(I1,FIND(",",I1)-1)

Regards,
Barrie

Posted by Jimmy on September 05, 2001 12:42 PM

Thanks, Barry But.....

Thanks, Barry works great, but know do I remove these words and any commas and/or spaces that is left before teh text in the orginal cell

Posted by Barrie Davidson on September 05, 2001 12:51 PM

Re: Thanks, Barry But.....

To get the rest of the cell information (Compaq 500 MH, and no space before this), use

=RIGHT(I1,LEN(I1)-FIND(",",I1)-1)

Is this what you need?

Barrie

Posted by Jimmy on September 05, 2001 1:02 PM

Re: Thanks, Barry But.....

Thanks again Barry, yes that does work, I guess what I was really wanting was to leave this text in the orginal cell and just delete the unwanted spaces and comma before the text. Unless I mis-understand (and thats not to hard for me)I need to place this formula in another cell and then delete the orginal cell.

Thanks for all the help

Posted by Eric on September 05, 2001 1:13 PM

another way to skin the cat, text to columns

Don't know if all of your data would fit this format, but if you've only the one comma in the text, then you can highlight the column (or part of the column) containing the text, and click Data-->Text to Columns, and click the "delimited" radio button, then select "comma" as the delimiter and click "OK".

You should get what is before the comma in the original column (call it Col-A), and what is after the column in the adjacent column (Col-B in this example). If you have extra spacing at the front of the second column, you can put the formula
=trim(b1)
in C1 and copy down as needed- it will take spaces from the beginning or end of text without taking them from between words in the string.

Hope that helped

Posted by Barrie Davidson on September 05, 2001 1:16 PM

In that case, have a look at Eric's response (NT)