When preparing to import information into Infocapture, data must be prepared in a way that the software will accept.
If your form has a list, Infocapture will expect the select list ID values, rather than the textual data.
For example, your form has the following list:
YOUR LIST FROM THE FORM valuevalue,#001 baluebalue,#002 haluehalue,#003 taluetalue,#004 galuegalue,#005
And you are trying to import the following textual data:
TEXTUAL DATA valuevalue baluebalue valuevalue baluebalue haluehalue baluebalue valuevalue valuevalue baluebalue galuegalue
In Excel, create four new columns to the right of the textual data column. These are temporary and will be deleted at the end. Copy your list into the first spare column.
Let us now suppose that
Column A contains the textual data you are looking to replace with list ID values
Column B contains your list of values from the form
Column C is going to be containing the list's available values without their ID numbers
Column D is going to be containing the list's available ID numbers without their textual values
Column E is going to be your last formula that wil return your list IDs you're trying to find.
In column C, write
This will trim the right 5 characters (the ID number and the comma), leaving only the text.
In column D, write
This will take the right 3 characters, leaving only the numerical ID value.
If done correctly, you should be left with something similar to this:
Now, in column E we need to write a 'vlookup' formula that will:
- Search our column A values, line by line, from column C
- Return the corresponding value from column D
- Dynamically change the cell it's using as search criteria with each new line
- But not change the search area, this must remaing fixed
This is written as
This is saying lookup what's found in cell A1, within the range C1 to D5 and don't change those cell references (indicated with the dollar signs), if a match is found return the second column (i.e. column D), finally return an exact match only (if 'true' rather than false it will look for partial matches).
Copy this formula all the way down the column, in line with however many cells are in use in column A.
If done correctly, column E will now have the list ID values of everything from column A, like so:
Finally, copy your data in column E, and paste over column A. However - ensure you right click, choose Paste Special Values so that the data is pasted, and not the formulas.
You can now delete your temporary columns B to E.