Pulling data based on beginning cell values

kiritine

New Member
Joined
Apr 15, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Hello,

I have a file with over 13,000 lines that I need to pull some data from for another report. The 1st column has a variety of codes with roughly 5 different prefixes (i.e. 4300******, A********, etc). I need to pull all the data for a few prefixes. The data is all unique (wild characters) after the prefixes.

I've tried text to columns, filter functions, ifs, match, vlookups, etc. If anyone has some ideas, then that would be a great help.
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,560
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Board!

One simple way would be to insert a new column, and use the LEFT function to pull off the first few characters to get the prefix, i.e.
=LEFT(A1,4)
and then you can filter on this calculated column.
 

kiritine

New Member
Joined
Apr 15, 2021
Messages
2
Office Version
  1. 365
Platform
  1. Windows
Welcome to the Board!

One simple way would be to insert a new column, and use the LEFT function to pull off the first few characters to get the prefix, i.e.
=LEFT(A1,4)
and then you can filter on this calculated column.
Hi! Thank you for your help. Would I be able to use the left function to pull multiple prefixes in one formula? Maybe if it were nested?

A number of the people I work with have extremely limited excel knowledge and skills, so I'm trying to limit how much they need to modify to prevent things getting missed or formula breaking.
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
57,560
Office Version
  1. 365
Platform
  1. Windows
Hi! Thank you for your help. Would I be able to use the left function to pull multiple prefixes in one formula? Maybe if it were nested?
I think you may be confused by what the LEFT function does. It simply returns the first "x" number of a string. It does not care what they start with.
So the one example I gave you will pull the first 4 characters of whatever is in cell A1.

It might be best if you list out all the actual prefixes (you said there was only about 5), so we can see what they look like.

Note that if you need to automate stuff for people, you can use VBA for that.
 

Watch MrExcel Video

Forum statistics

Threads
1,132,914
Messages
5,655,935
Members
418,253
Latest member
TheJackal26

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