Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reusing formula | Excel Discussion (Misc queries) | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
Match then lookup | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions |