This is basically a follow-up to my question from last week.
I was able to achieve this result with Mark W's help:
What I need to do now is create a formula that will be applied to the original L column and extract only the project codes that are invalid and put them in a seperate column. A valid project code is 5 numeric digits in one contiguous string (no spaces) with no other characters in the cell. (ie- 43023 and nothing else). Many cells have data such as 43023 KEWO 342 or ODK32402-2. These are invalid and we need to seperate them in order to contact all vendors who are supplying incorrect project codes.
Can I somehow modify Mark W's functions to achieve this or is there another easier function I can use? Any help is appreciated.
This message was edited by kamil on 2002-07-15 08:02
Bottom line is this:
I have 5,000+ cells in a document with over 5,000 rows and columns up to the letter Q. The column I am working with is L. I need to remove all text in the 5,000+ cells and leave only numeric values. The placement of the text and numeric values within the cells is random.
I was able to achieve this result with Mark W's help:
If L1 contains "012321 BGH YRK", enter the array formula...
{=LEFT(L1,MAX(IF(ISNUMBER(MID(L1,ROW(INDIRECT("1:"&LEN(L1))),1)+0),ROW(INDIRECT("1:"&LEN(L1))))))}
...into M1, and the array formula...
{=RIGHT(M1,COUNT(IF(ISNUMBER(MID(M1,ROW(INDIRECT("1:"&LEN(M1))),1)+0),1)))}
...into N1. Fill down the formulas in M1 and N1 as needed. Your substrings containing only numeric characters are in column N. This solution assumes that there will only be 1 contiguous set of numeric characters per text value in column L.
Note: Array formulas must be entered using the Control+Shift+Enter key combination. The outermost braces, { }, are not entered by you -- they're supplied by Excel in recognition of a properly entered array formula. For more on array formulas see the Excel Help topic for "About array formulas and how to enter them".
What I need to do now is create a formula that will be applied to the original L column and extract only the project codes that are invalid and put them in a seperate column. A valid project code is 5 numeric digits in one contiguous string (no spaces) with no other characters in the cell. (ie- 43023 and nothing else). Many cells have data such as 43023 KEWO 342 or ODK32402-2. These are invalid and we need to seperate them in order to contact all vendors who are supplying incorrect project codes.
Can I somehow modify Mark W's functions to achieve this or is there another easier function I can use? Any help is appreciated.
This message was edited by kamil on 2002-07-15 08:02