Numbers as Text in Cross Reference Formulas

Cleversheet

If you need to search for a textual numeric in a cross-reference formula, you may need to use the CONTAINS function when the host source (ie, ReferenceSheetPickMatchColumn) uses an apostrophe to treat the number as a text value, like this:

=SUMIFS(ReferenceSheetPickContentColumn, ReferenceSheetPickMatchColumn, CONTAINS([CurrentSheetMatchColumn]@row, @cell))

This probably applies to all cross-reference cases, though I've tested only SUMIFS at this point.

Background: I had a column in the reference sheet containing textual numbers (eg, ‘44021 and ‘00000). SUMIFS was not able to find matches due to the leading apostrophe until a combination of the CONTAINS and @CELL functions was added to the formula. This solution was found for me by Genaro on the Enterprise Support team after he and I had spent nearly an hour trying all sorts of other ways to yield the expected result—and that was after I'd spent over an hour before calling him to try everything I could think of (eg, using a formula to force an apostrophe in front of the searched number; adding an apostrophe to the searched number on the formula sheet; creating a separate column in the reference sheet to strip off the apostrophe [using the VALUE function—but for account coding purposes I needed a five-zero account segment, so I could not just convert the column to strictly numerics], and so on).

Paul Newcome

This is a good idea. The only thing I can think of right off that MIGHT cause issues is if you do not have a standardized character count.

CONTAINS("00100", @cell)

will grab "00100" as well as "100100" or "001001".

Of course using leading zeros means that is very unlikely to be an issue, but it is one thing to keep in the back of your mind when using the CONTAINS function.

Marinajo

I have this issue

I am using a nested if-statement as a column formula where two of the values are sometimes text and other times saved as values. These are budget numbers so one example is 23001 & 23001F which are two different funds. I have tried both with and without CONTAINS, but the row for 23001 is either grabbing the value for 23001F (if set up as text ("23001) or nothing if set up as number (23001). And 23001F does not show up unless I use CONTAINS.

Here is my two versions of the formula: =SUMIFS({Allocation}, {Fund}, CONTAINS(Fund@row, @cell))

Cleversheet

@Marinajo , I wonder if it could help to create a helper column that turns both 23001 and 23001F into text values, such as ="'"+[budget number column]@row. I'm not certain, though, what might result from @Paul Newcome's observation that the character count might create an issue. If so, maybe use an IF that adds a trailing zero as well as a leading apostrophe when the [budget number column] value is numeric? Just thinking out loud here...

Paul Newcome

@Marinajo Yes. You definitely want all data to be of the same type. Using a helper column and converting all entries into text should help. @Cleversheet's solution with the apostrophe may work, but I have seen those things cause problems on the back-end.

I generally use this (just in case):

=[Column Name]@row + ""

Cleversheet

@Paul Newcome, good point. The simple double quotes should suffice to convert a numeric budget number into text in the helper column, so the apostrophe I inserted between them is likely superfluous. Thanks for catching that little detail!

@Marinajo, be sure to read this far down in the thread.

Marinajo

Thank you both, it will give it a try and report back :)

Marinajo

IT WORKED :) Thank you very much for your help :)

Paul Newcome

Happy to help. 👍️

Nathan R

Hello,

Is it possible to use that formula =[Column Name]@row + "" but also have it include zeros after a decimal place?

I'm getting an issue with decimal places dropping off new employee contracts and need to work out how to display:

$27 as $27.00

and $27.5 as $27.50

I'm aware of using find to add .00 after a number but can't work out how to account for both iterations

any help would be greatly appreciated

Cleversheet

@Nathan R, the easiest way would be to create a helper column that uses =VALUE([source column]@row) where $27 is converted from text to number, then set your column display to $ format, specifying to desired number of decimals. If for some reason that doesn’t work you might need to strip off the dollar sign first using MID([source column]@row,2,99) within the VALUE statement. Worst case, you may need to write a nested IF that counts the number of characters to the right of the decimal using LEN in combination with MID and FIND to locate the decimal point.