Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I am looking to query some variables in Excel using a formula. I have a database that has some IDs in it that I need pulled out. The problem is that the IDs vary in length and type.
Here are some examples of the database, bare with me:

Identification Key: ID000012 DESCRIPTION: DO this here in terms of company policy.

Another example:

Identification version 5.1.1 Audit ID: 11123

Last example:

ID: A-12345 Description: DeltaDelta - Download ASAP

See how the IDs all differ just slightly but I'm wanting to pull basically the same information out of each. Is there a formula that can be written to encompass finding "ID000012", "11123" and "A-12345" all these in one?
These are just variables, there are thousands of IDs that I need to pull but I figured giving real life examples would make it easier to see what I'm trying to do here.

Any help greatly appreciated!

share|improve this question
1  
What type of database is this, an Excel workbook or a true database file (access, sql, etc.)? What have you tried so far? –  CharlieRB Dec 20 '12 at 20:46

1 Answer 1

There is too little format consistency for even a formula that worked on the three examples to appear to be likely to work very well for the thousands, so a 'compromise' might be to accept extraction of only the first nine characters after "ID" and adjust further 'manually' to suit. Assuming the first example is in A1 and others immediately below:

     =MID(A1,FIND("ID",A1)+2,9)

in say B1 may be a start, copied down as far as required.

Sort on the result, Copy/Paste Special/Values followed by Replace space by nothing and Replace colon by nothing.

Trim off appended excess with:

    =LEFT(B1,FIND(" ",B1))
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.