We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

Formula picks out individual digits not whole strings

asked 2018-11-27 23:14:20 +0200

Larry in Waterloo gravatar image

Hello. I am using the latest version of LO in Windows 10. I am attaching a condensed version of my .ods file for reference. Columns A to F are my data, and the formulas are in columns H to N and O to U. The formulas in columns H to N refer to data in row 1 and the formulas in columns O to U refer to data in row 2. I want an 'X' to print in the formula columns if the number (1 to 49) corresponds to any data in the respective data rows. It sort of works, but it is picking out single digits from the data instead of the whole numbers, and wrongly marking an X for the single digits. For example, there is an X in cell H3 because cell E1 (23) contains a 3; and the formulas in columns O to U show an X for 2 and 3 because of the 21 and 38 in row 2. I have marked the offending cells in orange.C:\fakepath\Sample for Reference.ods

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted

answered 2020-07-20 22:17:09 +0200

LeroyG gravatar image

Don't use "*"& nor &"*" next the number you are looking for. You can Find & Replace unchecking Regular expressions.

So H1 formula must be: =IF(COUNTIF($A$1:$F$1;"1");"X";"")

Instead of: =IF(COUNTIF($A$1:$F$1;"*"&1&"*");"X";"")

Edit your question if you want to add more information; also can comment an answer (Add Answer is reserved for solutions).

Check the mark (Correct answer mark) to the left of the answer that solves your question.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-11-27 23:14:20 +0200

Seen: 32 times

Last updated: Jul 20 '20