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 will return the list of IDs you're trying to find.

In column C, write

=LEFT(B1, LEN(B1)-5)

This will trim the right 5 characters (the ID number and the comma), leaving only the text.

In column D, write 

=RIGHT(B1,3)

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 remain fixed

This is written as 

=VLOOKUP(A1,$C$1:$D$5,2,FALSE)

This is saying lookup what's found in cell A1, within the range C1 to D5 and doesn'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, and choose Paste Special Values so that the data is pasted, and not the formulas.

You can now delete your temporary columns B to E.