Archive of Mr Excel Message Board


Back to Forms in Excel VBA archive index
Back to archive home

=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


Check out our Excel Resources

Re: =Left(I1,???),

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

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

Regards,
Barrie


Thanks, Barry But.....

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

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


Re: Thanks, Barry But.....

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

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


Re: Thanks, Barry But.....

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

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


another way to skin the cat, text to columns

Posted by Eric on September 05, 2001 1:13 PM
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


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

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


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.