I have a cell that is combined with a series of numbers and text that
describes some pipe information. I want to be able to extract the pipe size
and lenght numbers into another column and remove from text cell so they are
separate columns. Ex: 10' x 0-6" Ductile Iron CL53 SPL Pipe Expoxy Coated.
Any help!

Hi!

If the format is ALWAYS the same:

=LEFT(A1,FIND("""",A1))

Biff

"Greg" wrote in message
...
I have a cell that is combined with a series of numbers and text that
describes some pipe information. I want to be able to extract the pipe
size
and lenght numbers into another column and remove from text cell so they
are
separate columns. Ex: 10' x 0-6" Ductile Iron CL53 SPL Pipe Expoxy
Coated.
Any help!

Application of the LEFT, RIGHT, or MID functions could easily extract out the
data you require, but depend of consistant formatting of the
information........for example, =LEFT(A1,10) would get 10' x 0-6" out of the
sample you provided (if it were in A1), but may not be applicable for other
lines...........we would need several sample lines of data to maybe come up
with something better.......

Vaya con Dios,
Chuck, CABGx3


"Greg" wrote:

I have a cell that is combined with a series of numbers and text that
describes some pipe information. I want to be able to extract the pipe size
and lenght numbers into another column and remove from text cell so they are
separate columns. Ex: 10' x 0-6" Ductile Iron CL53 SPL Pipe Expoxy Coated.
Any help!

Yes it is at least the size length info is always in front but could vary
from 10-12 charcters. However the text is either 1 to 2 spaces out so the
text lines up. My question to you bill is the (""",A1)) quotations represent
the number of spaces needed to extract. ?

"Biff" wrote:

Hi!

If the format is ALWAYS the same:

=LEFT(A1,FIND("""",A1))

Biff

"Greg" wrote in message
...
I have a cell that is combined with a series of numbers and text that
describes some pipe information. I want to be able to extract the pipe
size
and lenght numbers into another column and remove from text cell so they
are
separate columns. Ex: 10' x 0-6" Ductile Iron CL53 SPL Pipe Expoxy
Coated.
Any help!

In light of that, then as an alternative you could use Data TextToColumns
with a fixed delimiter set 12 characters in from the left edge and do not
import the rest of the description..........then if you have any alpha
characters left in that column, ASAP Utilities (free at
www.asap-utilities.com) can remove them. and if you're doing this
frequently, it can all be written in to a macro and done with the push of a
button........

Vaya con Dios,
Chuck, CABGx3





"Greg" wrote:

Yes it is at least the size length info is always in front but could vary
from 10-12 charcters. However the text is either 1 to 2 spaces out so the
text lines up. My question to you bill is the (""",A1)) quotations represent
the number of spaces needed to extract. ?

"Biff" wrote:

Hi!

If the format is ALWAYS the same:

=LEFT(A1,FIND("""",A1))

Biff

"Greg" wrote in message
...
I have a cell that is combined with a series of numbers and text that
describes some pipe information. I want to be able to extract the pipe
size
and lenght numbers into another column and remove from text cell so they
are
separate columns. Ex: 10' x 0-6" Ductile Iron CL53 SPL Pipe Expoxy
Coated.
Any help!

is the (""",A1)) quotations represent the number
of spaces needed to extract. ?

It's 4 double quotes: """"

No, it has nothing to do with spaces. It's looking for the double quote that
represent the inches:

10' x 0-6|"| Ductile Iron CL53 SPL Pipe Expoxy

When (if) it finds the double quote, it returns everything to the left. I'm
assuming every entry contains a double quote. That's why I asked if the
format is ALWAYS the same.

Biff

"Greg" wrote in message
...
Yes it is at least the size length info is always in front but could vary
from 10-12 charcters. However the text is either 1 to 2 spaces out so the
text lines up. My question to you bill is the (""",A1)) quotations
represent
the number of spaces needed to extract. ?

"Biff" wrote:

Hi!

If the format is ALWAYS the same:

=LEFT(A1,FIND("""",A1))

Biff

"Greg" wrote in message
...
I have a cell that is combined with a series of numbers and text that
describes some pipe information. I want to be able to extract the pipe
size
and lenght numbers into another column and remove from text cell so
they
are
separate columns. Ex: 10' x 0-6" Ductile Iron CL53 SPL Pipe Expoxy
Coated.
Any help!