Extracting Only Certain Data -- Need help with a function.

[kamil]

This is basically a follow-up to my question from last week.

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

 

[kamil]

ttt -- anyone?

 

[Mark W.]

kamil, you could just check the length of the value in column N using...

=LEN(N1)

A length other than 5 would be invalid.

 

[kamil]

Unfortunately due to the complexity of the previous functions I wasn't able to learn anything of how they work. How would I apply this LEN function to the entire L column?

 

[kamil]

I know what the function should do but unfortunately I am not familiar with the syntax in excel at all. Basically the new column should perform a LEN on each respective cell in column L and then take the value from the cell only if its LEN result is greater than 5. Now how do I write this?
This message was edited by kamil on 2002-07-15 09:10

 

[Mark W.]

No, you should examine the length of each value in column N. Simply enter =LEN(N1) into the next unused column and fill down as needed. You can now apply an AutoFilter to your list using the new column to display all column N values whose length is not 5.

 

[kamil]

Excellent -- you are invaluable Mark! Saving me alot of time and headache here. Many thanks!