Is there a way to convert text to number within a filter function?

[helpneeded2]

I am new to the many features that Excel 365 offers and I am currently learning the many options that are available through filters.

Currently I have a raw data sheet where I copy my info from an SQL export pull and paste it there. (I don't have access to the SQL script itself).

All of the numbers that are exported into Excel are exported as text. These text numbers need to be automatically converted to actual numbers. (Not by using the manual convert text to numbers option).

Currently, the only way I can think of to do this is to create and hide a sheet that would the link in all the data from the raw data sheet, and would do any required clean-up, such as TRIM spaces and convert text to numbers via VALUE().

Then I would have a third sheet which is the display/output sheet which grabs all the amended data in the hidden sheet and applies my FILTER.

Here is the formula I am using to pull in the data direct from my raw data sheet, and I was wondering if there is anyway to output certain columns as if I had used VALUE(), rather than having to resort to making the hidden sheet for converting the data.

=FILTER(INDEX(table_jackpot,SEQUENCE(ROWS(table_jackpot)),XMATCH(A1:P1,table_jackpot[#Headers])),table_jackpot[Date  Time]<>"")

 

[Fluff]

You could try

=LET(f,FILTER(INDEX(table_jackpot,SEQUENCE(ROWS(table_jackpot)),XMATCH(A1:P1,table_jackpot[#Headers])),table_jackpot[Date  Time]<>""),IFERROR(f+0,f))

 

[helpneeded2]

You could try

=LET(f,FILTER(INDEX(table_jackpot,SEQUENCE(ROWS(table_jackpot)),XMATCH(A1:P1,table_jackpot[#Headers])),table_jackpot[Date  Time]<>""),IFERROR(f+0,f))

Thanks, that seems to be working great. I'm really not familiar with the LET function, but it certainly seems to do the trick here.

 

[Dave Patton]

N.B. The Let saves the result of the formula; the plus 0 in "IFERROR(f+0 " coerces the text to value.

What happens if you use double negative to coerce the value?

=--FILTER(INDEX(table_jackpot,SEQUENCE(ROWS(table_jackpot)),XMATCH(A1:P1,table_jackpot[#Headers])),table_jackpot[Date  Time]<>"")

 

[Fluff]

What happens if you use double negative to coerce the value?

You get #value errors if there is any text in the data.

Thanks, that seems to be working great

You're welcome & thanks for the feedback.