=Sheet1!A2

=Sheet1.A2

Transfers the value of any numeric or date cells. Also used if every cell in a column of text data is expected to have an entry. If some text data is blank, see the "ISBLANK()" example below.

=TEXT(Sheet1!A2,"00000000")

=TEXT(Sheet1.A2,"00000000")

Converts a numeric value to a text value with a width of 8 characters and padded with leading zeroes. Useful when formatted codes (such as budget or account codes) were incorrectly converted to simple number values when they were pasted into a spreadsheet or opened with one. For example, this formula converts the number 1234 to  the text "00001234".

=TEXT(Sheet1!A2,"#")

=TEXT(Sheet1.A2,"#")

Use this to handle cells like model numbers which may or may not contain text characters. If the model number is a pure number, this will convert it to a left-justified text equivalent, but not change alphanumeric model numbers. If your particular spreadsheet software interprets the single "#" as only one character, add as many "#" characters as the width of the field.

Note: If numeric-only model numbers include a decimal point, try "#.#####" or similar to include numbers on the right side of the decimal point. Be sure to check your results.

=IF(ISBLANK(Sheet1!A2),"",Sheet1!A2)

=IF(ISBLANK(Sheet1.A2),"",Sheet1.A2)

Transfers the value of a text cell but shows a blank if the cell is empty, instead of a zero. This avoids importing empty text cells as "zeroes" instead of blanks when a formula has to be used.

=Sheet1!A2&"-"&Sheet1!B2

IF(ISBLANK(Sheet1!A2&Sheet1!B2),"",
Sheet1!A2&"-"&Sheet1!B2)

=Sheet1.A2&"-"&Sheet1.B2

IF(ISBLANK(Sheet1.A2&Sheet1.B2),"",
Sheet1.A2&"-"&Sheet1.B2)

Concatenates the value of two cells (such as two separate codes) together, with a "-" between them. E.g. "123-12345" This might be used where two fields (columns) in the original data need to be combined into a single FF&EZ field, but with a visual separation. The "&" is called the "concatenation operator" and adds pieces of text together.

The second example combines this with the previous type of formula to leave the result blank if both source cells are blank (so you don't import just a "-").

=UPPER(LEFT(Sheet1!A2,10))

=UPPER(LEFT(Sheet1.A2,10))

Returns the first 10 characters of a cell contents, in upper case. This can be useful for creating temporary Vendor IDs from a vendor's name if none exist in the original data. Be careful to avoid duplicate Vendor IDs when two similar but different vendors are in the data.

This technique may produce acceptable Vendor IDs, but you can edit the ID later on the Vendors screen is desired.

Replace simple line feed:

=SUBSTITUTE(Sheet1!A2,
CHAR(10),"<BR>")

=SUBSTITUTE(Sheet1.A2,
CHAR(10),"<BR>")

Replace CR/LF:

=SUBSTITUTE(Sheet1!A2,
CHAR(13)&CHAR(10),"<BR>")

=SUBSTITUTE(Sheet1.A2,
CHAR(13)&CHAR(10),"<BR>")

This strips out all embedded line feeds from a long description and substitutes the HTML break code "<BR>" for it. This prevents an incorrect import of the final text file due to a false "new record" being started by the line feed. FF&EZ will convert the "<BR>" back to a line feed after the import is done. Note that you may also be able to use "search and replace" to replace the line feeds in the original data (see below).

Second example: If the data contains full CR/LFs instead of just a line feed (LF),  the formula may not seem to work. Try

CHAR(13)&CHAR(10)

as the search expression in the formula instead. Note: When paired, they are always used in the order shown.

=IF(LEN(Sheet1!A2)>50,"!!!!!","")

=IF(LEN(Sheet1.A2)>50,"!!!!!","")

This isn't a data transfer formula, but a "flag" formula that could be placed in a new column next to a source data column to flag cells whose contents are too wide for the database field (here, the test is for those greater than 50 characters). If the tested content is too long, this cell displays exclamation points (!!!!!) so that you know the referenced cell in that row needs special attention.

If you have experience with "conditional formatting," this technique can be used within the data cells to highlight problems, too. In that case, the condition would be

LEN(Sheet1!A2)>50

and that would be used to select the format you define for it.

Conditional format test:

AND(UPPER(LEFT(A2,10))=
UPPER(LEFT(A3,10)),
UPPER(A2)<>UPPER(A3))

AND(UPPER(LEFT(A2,10))=
UPPER(LEFT(A3,10)),
UPPER(A2)<>UPPER(A3))

This is not a cell formla but a formula that can be used for conditional formatting in the source sheet's cells after sorting by vendor. It uses the formula (referenced earlier) to create a ten-character expression (like you might use to create a Vendor ID) and the formula for the full value of cells to answer this question:

"Is the ID that would be created from this cell (A3) equal to the one that would be created above it (A2), but the full value of this cell is actually different from the one above it?"

The AND() function returns "True" (triggering the format) if both conditions separated by the comma are true. The UPPER() function is used to ensure that all characters are compared without regard to upper or lower case.

Using this formula in a conditional format that changes the color of the current cell (A3 in this example) would flag a cell in which the resulting ID was the same as the one above it, but the full value (such as the full vendor name) in the two cells was different.

When using conditional formats and copying them to other cells in a column, be sure to copy the format not the contents of the original cell. This can be done with Edit> Copy special or with a "copy format" function.