Querying variables in Excel

1

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:

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.

Tyler

Posted 2012-12-20T20:31:59.723

Reputation: 11

1What type of database is this, an Excel workbook or a true database file (access, sql, etc.)? What have you tried so far? – CharlieRB – 2012-12-20T20:46:12.053

Answers

0

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))

pnuts

Posted 2012-12-20T20:31:59.723

Reputation: 5 716

Asked: 2012-12-20T20:31:59.723

Viewed: 110 times

Active: 2019-09-26T14:47:41.603