↑↓ selectEnter openEsc close

    1. Elbuz

    Column address: how to join columns, take the first non-empty one or set fixed text

    4 min readUpdated

    The column address tells the program which file column a field takes its value from. Besides a letter or number, it can join columns, take the first non-empty one or insert the same text for all products — no formulas.

    When you need this:

    • Name in two columns — B,{ },D joins them with a space.
    • Stock split by warehouse — H,I,J adds up the numbers.
    • Price in one column or another — D,E takes the filled one.
    • No brand in the price list — {HP} inserts it for all products.

    Where to start: “Columns” section of the source card → “Column address” cell of the field.

    What a column address can do

    How to write an address and join columns

    The hint in the address field is short: “Address: A-ZZ / number. Composite: A, { }, B. Range: 7-9. First non-empty: 11|12”. The detailed help next to it covers each case separately.

    The easiest place to get a column letter is the file preview: each column shows its letter with the number below. Hover over a column to highlight it; the cell tooltip shows the address and value, e.g. “I33: 6299”. Write only the letter in the address: I.

    The second and third forms are the same feature: building a value from several columns and fixed text. List the parts separated by commas. Anything in curly braces is inserted as is, without the braces. Everything else is column numbers and letters.

    Values from adjacent columns are joined with no space. If column A contains “DeLonghi” and column B contains “ECAM 22.110”, the address A,B gives “DeLonghiECAM 22.110”. The program doesn't add a separator on its own: the value goes into the field exactly as it was built.

    Set the separator as text in curly braces. A,{ },B gives “DeLonghi ECAM 22.110”, and A,{/},B gives “DeLonghi/ECAM 22.110”. A comma, hyphen or any other character works the same way.

    In “Name”, the program removes repeats from adjacent columns itself. With “SMEG” in one column and “SMEG BLF 03 WHEU” in the next, 4,{ },5 gives “SMEG BLF 03 WHEU”, not “SMEG SMEG BLF 03 WHEU”: if a value starts with or contains the previous one, the repeat and the text between them are dropped.

    Matches are checked by whole word, case-insensitively, and values without letters — codes like 12,{-},12 — aren't compared. In other fields, the parts of a composite address are joined as is, without this check.

    Address examples:

    AddressResult
    B, { }, Dvalue of column B, a space, value of column D
    {TEKA }, Bthe brand name before every value of column B
    {HP}the same text for all products; the file has no such column at all
    2, { }, 4the same, with column numbers instead of letters
    7-9range: same as 7,8,9
    11|12first non-empty: the value from column 11, or from 12 if 11 is empty

    How price, quantity and category read a composite address

    Three fields don't join values — each has its own logic instead, the one you'd expect from it. Everything below applies to Excel, and ; for how an XML feed reads a composite address, see the XML article.

    • Price. Values aren't joined. The first value that is neither empty nor zero is used. This handles a price list where the price is sometimes in one column, sometimes in another: D, E, F means “take whichever is filled”.
    • Quantity. Values are added up. Three warehouses in three columns — the address H, I, J gives total stock. Only digits and the minus sign are taken from a cell, so “5 pcs” is read as 5.
    • Category from the price list. The category separator goes between values, so you get a path, not a string. If the same character appears inside a value, the program replaces it so it doesn't look like a nesting level.

    Where you can edit the address

    PlaceEditable?
    Address badge in a wizard rowonly for a manual column — one you added yourself. For a column from the file, the file itself set the address, and editing it would point the field at another column
    “Configure column” panel, “Column address” fieldyes, for any format; for and the format check doesn't apply
    “Add column” form at the bottom of the wizardyes, you type the address manually
    “Column address” cell in the card's field gridyes, any address, including an XML tag name
    “Adding a field” dialogyes, when you create the field
    The Add column form in the column mapping wizard: system field picker, address field with format hint, Add and Cancel buttons
    1System field picker
    2Address field with format hint
    3Add button
    1. System field — choose which system field this column fills.
    2. Address — type a column letter, number or composite address; the hint shows the allowed formats.
    3. Add — creates the column; use Cancel to close the form without saving.

    Use the “Add column” form when a column isn't visible in the file but the file has its values: for example, the preview doesn't show all columns, or you want a field with fixed text.

    Choose the field first, then type the address. If you click “Add” without a field, the program says “Select a field”, and if you click the address badge in a row that has no field assigned yet, it says “Select a field first”.

    The format check accepts letters A-ZZ, numbers up to three digits, and text in curly braces. It parses a composite address the same way the server does: a trailing comma and a comma inside curly braces don't trip it — 2, { }, 4, and {, } pass.

    The only thing it rejects is an empty address. For an XML source the address is a tag name (vendorCode, [available]), and the panel knows this: the format check doesn't apply to such sources at all. An empty address is still rejected.

    How to combine two price columns in different currencies

    Some price lists have the price in two columns, euros and hryvnias, and each product has one of them filled. One address can't map such a file — but it's easy, because currency is set per column, not for the whole price list. That's what the “Currency” group in the “Configure column” panel is for.

    Here's how:

    1. Map the main price column to the “Price” field and set its currency in the column panel.
    2. Create a custom field of the “Price” type for the second column — say, “Price EUR” — and set its own currency.
    3. Combine them with a formula: IF({Price list: Price} > 0, {Price list: Price}, {Price list: Price EUR}) — whichever of the two is filled goes to the “Price” target.

    Don't convert the currency in the formula. The program applies the column's rate during import, so the price list product gets the converted amount. The formula only picks one of the two values. If the column has no rate, the currency directory rate is used; the “+%” markup in the column panel adjusts it for this supplier.

    Stock spread across several columns

    A similar task: the supplier gives stock not as one number but by warehouse — three columns “Kyiv”, “Lviv”, “Odesa”. It works the same way, with custom fields:

    1. Create three custom fields of the “Number” type — for example “Stock1”, “Stock2”, “Stock3” — and give each its own column address.
    2. In the “Quantity” target, enter a sum formula: {Price list: Stock1} + {Price list: Stock2} + {Price list: Stock3}.

    The same formula works for the stock status field if what matters is not exact stock but simply “in stock / out of stock”. An empty column adds zero to the sum and doesn't spoil the total.

    Scenarios

    Four typical tasks solved not by a new field or a formula, but by the column address itself. In each case, enter the address in the “Column address” cell of the right row in the field grid of the “Columns” section.

    The product name is split across two columns

    The supplier puts the brand in one column and the model in the next, but you need the full name. In the “Name” row, enter B,{ },D: column B, a space, column D. The price list product gets “DeLonghi ECAM 22.110”, and the “Resulting value” column shows this before import.

    What won't happen: a space without { } — the address B,D joins the values with no space. But the program removes a repeated brand itself: if the model in the file already starts with the brand, the name contains it only once.

    Stock is spread across warehouses

    Three columns “Kyiv”, “Lviv”, “Odesa”, and the catalog needs one number. In the “Quantity” row, enter H,I,J or the range H-J. The values are added up, and only digits and the minus sign are taken from each cell, so “5 pcs” counts as 5. In an XML feed, tags for “Quantity” are added up the same way.

    What won't happen: for “Stock status (from price list)” the same address doesn't add anything — the values are joined into a string. If you need an “in stock / out of stock” status, use a formula, as described in stock spread across several columns.

    The price is sometimes in one column, sometimes in another

    Some products have “Wholesale” filled, the rest “Promotion”. In the “Price” row, enter D,E. Values aren't joined: the first non-empty, non-zero one is used. This applies to every price field, including “RRP”. For other fields, use the vertical bar: 11|12 takes column 11, or 12 if 11 is empty.

    What won't happen: this rule doesn't work in an XML feed — tags for the price are joined, and a vertical-bar list doesn't work there.

    The price list has no brand column

    The supplier carries one brand and doesn't list it in the file. In the “Manufacturer” row, enter {HP} — text in curly braces. Every product in this price list gets the manufacturer “HP”; no column in the file is needed. The address works both in a spreadsheet and in an XML feed.

    What won't happen: the program doesn't check whether the product is really of this brand. If the supplier adds another brand to the price list, it also comes in as “HP” until you change the address.

    What to do when an address isn't accepted

    Common problems
    What you seeWhyWhat to do
    “Address: letters A-ZZ or a number. Composite address — comma-separated, literal text in braces: A, { }, B. Range of columns: 7-9. First non-empty: 11|12” The address fails the format check. A is checked the same way: each part must be a letter, a number, a range (7-9), a vertical-bar list (11|12) or text in curly braces Fix the address: it can't be empty, and each part of a composite address must be a letter, a number, a range, a bar-separated list or text in curly braces (about the address)
    “Select a field” “Add” was clicked in the “Add column” form, but no field was selected Choose a field from the list and try again (where you can edit the address)

    FAQ

    How do I build a product name from two columns — for example, the brand from column 2 and the model from column 4, separated by a space?

    With a composite address in the “Name” row: 2,{ },4 or with letters B, { }, D. You don't need a CONCAT formula for this. More: composite address.

    How do I join the values of two columns without a space?

    List them separated by a comma with nothing in between: A,B. The program doesn't add a separator on its own. Need a character? Add it in curly braces: A,{-},B, A,{ / },B.

    How can I see what an address gives before import?

    Look at the “Resulting value” column in the card's field grid: it builds the value by the same rules as the import. More: the “Resulting value” column.

    Do range and “first non-empty” work in an XML feed?

    No. In a feed the address is a tag name, and hyphens occur in tag names themselves. Joining with text in curly braces works. More: XML mapping.

    How far can an address reach?

    By letter — from A to ZZ, by number — up to three digits. The address can't be empty.

    Where to find it and who has access

    Menu
    Directories → Processing price lists → List of price lists
    From the source card
    “Columns” section → “Column address” cell in the field grid
    Other entry points
    column mapping wizard: the “Configure column” panel, “Add column” — all places
    Access right
    the “List of price lists” window; changing addresses requires the right to edit price lists

    Related topics

    Was this article helpful?

    Missing a feature? Tell us.

    We will add it for free if other users need it too.

    If only you need it, we will agree on it separately.

    Article contents

    Suggest an improvement

    Try Elbuz free