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

helpneeded2

Board Regular
Joined
Jun 25, 2021
Messages
70
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

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
74,384
Office Version
  1. 365
Platform
  1. Windows
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))
 
Solution

helpneeded2

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

Dave Patton

Well-known Member
Joined
Feb 15, 2002
Messages
5,233
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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]<>"")
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,167,159
Messages
5,852,434
Members
431,507
Latest member
gatobarbieri

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
Top