Query for a numeric range in a string field

jeff_cp

Board Regular
Joined
May 21, 2015
Messages
66
I'm building a spreadsheet from a MSQuery that has a string field in the data. Inside this string field contains numbers and text. I need to only query a numeric range in this field. For example, the field can have values like 90019A or 903 or 908C or 900123. I need to query anything in a range of 900000 to 999999.

Is this possible?
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,011
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
You could bring the data into Power Query/Get and Transform. Split the column based upon the "Digit to Non-Digit" It is one of the native actions available. Then bring your file back to excel and do your analysis.
 

jeff_cp

Board Regular
Joined
May 21, 2015
Messages
66
I've never used Power Query. This spreadsheet will be distributed to other employees so would they need to have Power Query in order for it to work for them?
 

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,011
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Power Query is standard on 2016 and later. For versions 2010 and 2013, you will need to download the add-in from Microsoft. In the later versions it has been renamed "Get and Transform." To answer you question directly, it depends upon whether the worksheet is being distributed before or after the PQ actions have taken place. Shared or individual copies. Several variables that obviate a direct answer. Perhaps a little bit of investigation may be necessary. Click on the link in my signature for a starting point.
 

Watch MrExcel Video

Forum statistics

Threads
1,119,063
Messages
5,575,907
Members
412,689
Latest member
nhsmedic
Top