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

helpneeded2

Board Regular
Joined
Jun 25, 2021
Messages
110
Office Version
  1. 365
Platform
  1. Windows
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.

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

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
You could try
Excel Formula:
=LET(f,FILTER(INDEX(table_jackpot,SEQUENCE(ROWS(table_jackpot)),XMATCH(A1:P1,table_jackpot[#Headers])),table_jackpot[Date  Time]<>""),IFERROR(f+0,f))
 
Upvote 0
Solution
You could try
Excel Formula:
=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.
 
Upvote 0
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?
VBA Code:
=--FILTER(INDEX(table_jackpot,SEQUENCE(ROWS(table_jackpot)),XMATCH(A1:P1,table_jackpot[#Headers])),table_jackpot[Date  Time]<>"")
 
Upvote 0

Forum statistics

Threads
1,213,495
Messages
6,113,992
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top