Skip to main content

VALUE and TEXT function

Do you often have the problem that you create formulas "correctly" and the result is #NUMBER! or #VALUE! and not what you actually expected?

One reason for many error messages is that we want to compare "apples with oranges" within our formulas, i.e. compare columns or cells in text format with numbers and vice versa. However, this does not work and leads to an error message. Therefore, we have to pay attention to the form in which the data is available.

Reminder:

  • Data that is left-aligned is always data in text format.
  • Data aligned to the right are always numbers.

Please note:

Generally with cobby all attributes are output in type text. Exceptions are only price attributes that are formatted as general or standard and date attributes.

But how do numbers become texts or, conversely, texts become numbers?

Scenario: The initial situation is on the one hand a source file in which the SKU is specified as a number and a cobby export file in which the SKU is available as text. Now data are to be exchanged among each other or in one or the other direction via an index(match).

There are two options here:

  • Add a help column in your source data and convert the SKU to "Text" format. Insert the following formula:** =""& Data **
  • Use the VALUE or TEXT function. An advantage of these functions is that both source and cobby export data do not have to be changed.

VALUE function​

The VALUE function converts a certain value given as text into a number.

Example:

Goal: We want to transfer the name from the source file to the cobby export file.

Solution: We use the INDEX(MATCH) function:

=Index(“names”;Match((value(“[@[SKU]]”);”Sku from the source data”;0))

(value(“[@[SKU]]”): Here @[SKU] corresponds to the search criterion of the comparison, which is formatted by the VALUE function into the format Number.

TEXT function​

The TEXT function converts an argument given as a number into the format text.

Example:

Goal: We want to transfer the name from the cobby export file to the source file.

Solution: We use the INDEX(MATCH) function:

=Index(“description”; match(text([@[SKU]],0); Sku from the cobby export;0)

(Text([@[SKU]],0): corresponds to the search criterion of the comparison formatted into the format text