Text
 

a & a 24/03/09
a & a 24-26/09/09
a & a 3/06/09
a & a

I have a column of the above similar text. I would like help to write
formula to show text without the numbers. Formula would return for example a
& a

Thanks

Text
 

With your text in cell A1; try the below formula in B1

=TRIM(LEFT(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1& "0123456789"))-1))

If this post helps click Yes
---------------
Jacob Skaria


"nc" wrote:

a & a 24/03/09
a & a 24-26/09/09
a & a 3/06/09
a & a

I have a column of the above similar text. I would like help to write
formula to show text without the numbers. Formula would return for example a
& a

Thanks

Text
 

Thanks.

What about if I wanted to add character such as / i.e show text before /

How would that change your formula?


"Jacob Skaria" wrote:

With your text in cell A1; try the below formula in B1

=TRIM(LEFT(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1& "0123456789"))-1))

If this post helps click Yes
---------------
Jacob Skaria


"nc" wrote:

a & a 24/03/09
a & a 24-26/09/09
a & a 3/06/09
a & a

I have a column of the above similar text. I would like help to write
formula to show text without the numbers. Formula would return for example a
& a

Thanks

Text
 

If you mean extract text before the 1st slash
=LEFT(A1,FIND("/",A1)-1)


If this post helps click Yes
---------------
Jacob Skaria


"nc" wrote:

Thanks.

What about if I wanted to add character such as / i.e show text before /

How would that change your formula?


"Jacob Skaria" wrote:

With your text in cell A1; try the below formula in B1

=TRIM(LEFT(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1& "0123456789"))-1))

If this post helps click Yes
---------------
Jacob Skaria


"nc" wrote:

a & a 24/03/09
a & a 24-26/09/09
a & a 3/06/09
a & a

I have a column of the above similar text. I would like help to write
formula to show text without the numbers. Formula would return for example a
& a

Thanks

Text
 

Yes. Can the fomula include a list of predefined characters?

"Jacob Skaria" wrote:

If you mean extract text before the 1st slash
=LEFT(A1,FIND("/",A1)-1)


If this post helps click Yes
---------------
Jacob Skaria


"nc" wrote:

Thanks.

What about if I wanted to add character such as / i.e show text before /

How would that change your formula?


"Jacob Skaria" wrote:

With your text in cell A1; try the below formula in B1

=TRIM(LEFT(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1& "0123456789"))-1))

If this post helps click Yes
---------------
Jacob Skaria


"nc" wrote:

a & a 24/03/09
a & a 24-26/09/09
a & a 3/06/09
a & a

I have a column of the above similar text. I would like help to write
formula to show text without the numbers. Formula would return for example a
& a

Thanks