Descriptions seperation

[tr2usa]

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

 

[Biff]

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

 

[tr2usa]

Thank you for your help, I ment B1,C1. I copied to b1 but did not work
do you have any suggestion?

wrote:

 

[Biff]

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

 

[tr2usa]

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.

 

[Biff]

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

 

[tr2usa]

Biff,
This is perfect, it is a life saver. Thank you for your time and
effort.

Vedat

 

[Biff]

You're welcome. Thanks for the feedback!

Biff

Biff,
This is perfect, it is a life saver. Thank you for your time and
effort.

Vedat