For a "one off" formula solution(s)...

This formula inserts a comma at the desired location on the sample data you
posted.

A2 = 3/4" x 1/2" pvc pipe

=REPLACE(A2,LOOKUP(100,-MID(A2,ROW($1:$50),1),ROW($1:$50))+2,1,",")

Result of formula: 3/4" x 1/2",pvc pipe

These formulas will parse the string into 2 substrings.

A2 = 3/4" x 1/2" pvc pipe

Entered in B2:

=LEFT(A2,LOOKUP(100,-MID(A2,ROW($1:$50),1),ROW($1:$50))+1)

Result of formula: 3/4" x 1/2"

Entered in C2:

=MID(A2,LEN(B2)+2,50)

Result of formula: pvc pipe

--
Biff
Microsoft Excel MVP


"mgbcab" wrote in message
...
Ultimately, I want to do a text to columns method but 1st I need to make
the
data work properly.

I have close to 9000 items of vary degrees of numbers and text. I want a
formula that will insert a comma after the last " or ' in a cell, reading
from left.
3/4" x 1/2" pvc pipe
1/2" x 1/2" x 6' black pipe
1' insulated filter
This way when i do the text to columns, i can separate the by commas. The
purpose is to separate the sizes and the descriptions.