Forum Discussion

fvanhaelst's avatar
fvanhaelst
Copper Contributor
Jan 30, 2025

Asterisk doesn't represent any random character

Hi all,

I want to count all cells that start with '50' (see A7) in sheet Test scenarios_UR, column C. Result should be 9, and not 1.

According the documentation, the asterisk symbol represents any random characters. However, only when an asterisk symbol is typed in the cell, it's counted (see cell C6).

What do I wrong?

THanks for the help!

 

Kr,
F

excel

2 Replies

  • Patrick2788's avatar
    Patrick2788
    Silver Contributor
    Jan 30, 2025

    Keep your data as is and use REGEXTEST:

    =SUM(--REGEXTEST(A1:A10,"^50"))

    The "^50" is regex's way of checking for 50 at the start of the string.

  • Detlef_Lewin's avatar
    Detlef_Lewin
    Silver Contributor
    Jan 30, 2025

    The * only works in the context of (datatype) text. The formula only picks up the value in C6.

    Either you change your criteria to numbers or the data to text.

     

Resources