formula
 

Hi;
Can anyone guide me on how to extract the text data only?
The formula that I'm currently using is:
=(LEFT(INDIRECT("Sheet1!A"&ROW()),FIND(" ",INDIRECT("Sheet1!A"&ROW()))-1))

The above formula works well when the text data apperas before the numeric
value only. But, my requirements are as follows:

Example 1:
If the contecnt is " ABC- 123", I want it to show as ABC to me.
But, if the content is "ABC DEF GH-0987", I want it to be appreared as "ABC
DEF GH" to me. The symbol "-" and numeric value should be skipped

Can I add in the istext function into the above formula?Appreciate if you
could give me a green light on this. Thanks.

formula
 

Try this:

=SUBSTITUTE(A1,RIGHT(A1,SUMPRODUCT((LEN(A1)-LEN(SUBSTITUTE(A1,{0,1,2,3,4,5,6
,7,8,9,"-"},""))))),"")

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"ah" wrote in message
...
Hi;
Can anyone guide me on how to extract the text data only?
The formula that I'm currently using is:
=(LEFT(INDIRECT("Sheet1!A"&ROW()),FIND(" ",INDIRECT("Sheet1!A"&ROW()))-1))

The above formula works well when the text data apperas before the numeric
value only. But, my requirements are as follows:

Example 1:
If the contecnt is " ABC- 123", I want it to show as ABC to me.
But, if the content is "ABC DEF GH-0987", I want it to be appreared as
"ABC
DEF GH" to me. The symbol "-" and numeric value should be skipped

Can I add in the istext function into the above formula?Appreciate if you
could give me a green light on this. Thanks.