Home > database >  Removing 1st character of number before checking if its > 0, combined with indirect function
Removing 1st character of number before checking if its > 0, combined with indirect function

Time:10-28

I would like to use the =indirect() and =value() function together. I am to count how many numbers are > 0 in another sheet, in the rows I:I. In these sheets, the numbers are not formatted as values, and I am not to do this manually. In addition, for the numbers, they are formatted as " 123 " With a space before the number. I also need to remove the space in front the numbers in order for this to work.

In my current sheet, in cell J1, I have written "I:I". In the cell A3, I have the name of the sheet that I would like to count the numbers from. My attempt so far:

{=COUNT.IF(INDIRECT("'"&A3&"'!"&$J$1);">0")

However, this just returns values 0. This is because there is a space infront of the numbers. How do I remove this space? I need to implement it in this function

I've edited the post a bit, as I noticed there was a space infront of the numbers

CodePudding user response:

On a worksheet, if your value " 123 " is in cell A1 then you could use a formula like:

=VALUE(TRIM(A1))

...to TRIM the spaces, and convert the string to a numeric VALUE.


Or, in VBA, something like:

myValue = CInt(Trim(myString))

...to Trim the spaces, and Convert to CInt-eger.


Or, in many ad-hoc situations it's easiest to use "Text to Columns" to remove padded spaces (or any other character, really).

  • More ways to convert text to numbers here and here.

  • More ways to remove unneeded spaces here and here.

  • Related