 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / October 2006Descriptions seperation
I need to seperate the words and demensions from A1 to A2(all centence) and A3(Demensions). Can somebody help me. I do have 3000 items in my list. Thanks Oleo Ball,Multi,Large dia27" Pod Sculptural Vase,Amber,Large 32x9x41"h Pod Sculptural Vase,Amber,Medium 22x5x29"h Pod Sculptural Vase,Amber,Small Red Fence Tree,Black,Lg. 17x2.5x17"h Red Fence Tree,Black Md. 14x2.5x18"h Red Fence Tree,Black,Sm. 11x2.5x15"h Keyhole Sculpture,Amber,Medium dia9x30"h Keyhole Sculpture,Amber,Large dia8x34"h Flame Ceramic Sculpture,Black,Medium dia7x21"h Flame Sculpture,Black,Small dia9x19"h Ivory Stones Column,Medium 20x20x68"h Fossil Stones Column,Medium 20x20x68"h Leaf Vase,Round,Black/White dia11x17"h Leaf Vase,Cylinder,Black/White dia3x18"h Are you saying you want these results in A2 and A3: A1 = Oleo Ball,Multi,Large dia27" A2 = Oleo Ball,Multi,Large A3 = dia27" If so: Formula for A2: =IF(MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))>LEN(A1),"",TRIM(LEFT(A1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))))) Formula for A3: =IF(A2="","",TRIM(SUBSTITUTE(A1,A2,""))) Biff >I need to seperate the words and demensions from A1 to A2(all > centence) and A3(Demensions). Can somebody help me. I do have 3000 [quoted text clipped - 15 lines] > Leaf Vase,Round,Black/White dia11x17"h > Leaf Vase,Cylinder,Black/White dia3x18"h Thank you for your help, I ment B1,C1. I copied to b1 but did not work do you have any suggestion? wrote: > Are you saying you want these results in A2 and A3: > [quoted text clipped - 34 lines] > > Leaf Vase,Round,Black/White dia11x17"h > > Leaf Vase,Cylinder,Black/White dia3x18"h >Oleo Ball,Multi,Large dia27" Where does this data come from? Are the spaces between "Large" and "dia27"" standard char 32 spaces? If this string is in A1: Oleo Ball,Multi,Large dia27" Enter this test formula in some cell: =FIND(CHAR(160),A1) Let me know the result of that formula. I'll be around until about 3:00 AM EST. Biff > Thank you for your help, I ment B1,C1. I copied to b1 but did not work > do you have any suggestion? [quoted text clipped - 38 lines] >> > Leaf Vase,Round,Black/White dia11x17"h >> > Leaf Vase,Cylinder,Black/White dia3x18"h This was exported from ACCPAC accounting software. Space is not set some of them 3 some of them 18 spaces. the result is #Value!. The reson to seperate the dimension is to print show room labels in different rows and link than info to Website. > >Oleo Ball,Multi,Large dia27" > [quoted text clipped - 56 lines] > >> > Leaf Vase,Round,Black/White dia11x17"h > >> > Leaf Vase,Cylinder,Black/White dia3x18"h Ok, if that test formula returned #VALUE! then that means the spaces should be standard spaces so the other formulas should work. There is the possibility that the formulas got messed up as a result of line wrap or, I see that you're posting from Google Groups and Google Groups web pages can contain "garbage" that gets inserted into formulas. So, I've put together a sample file using your sample data: parse_strings.xls 18.5kb http://cjoint.com/?kngSiuqmc4 Biff > This was exported from ACCPAC accounting software. Space is not set > some of them 3 some of them 18 spaces. the result is #Value!. The reson [quoted text clipped - 63 lines] >> >> > Leaf Vase,Round,Black/White dia11x17"h >> >> > Leaf Vase,Cylinder,Black/White dia3x18"h Biff, This is perfect, it is a life saver. Thank you for your time and effort. Vedat > Ok, if that test formula returned #VALUE! then that means the spaces should > be standard spaces so the other formulas should work. There is the [quoted text clipped - 76 lines] > >> >> > Leaf Vase,Round,Black/White dia11x17"h > >> >> > Leaf Vase,Cylinder,Black/White dia3x18"h You're welcome. Thanks for the feedback! Biff > Biff, > This is perfect, it is a life saver. Thank you for your time and [quoted text clipped - 89 lines] >> >> >> > Leaf Vase,Round,Black/White dia11x17"h >> >> >> > Leaf Vase,Cylinder,Black/White dia3x18"h |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.