If a number exists as part of the cell, Then

Redwolfx

Well-known Member
Joined
Feb 22, 2013
Messages
1,161
Perhaps I'm making this more difficult than I need to.


I have a list of names generated by my system.

I'm doing a simple Vlookup to pull the names based off their ID in another sheet.

Some of the names start with a code X90 or U48 amonst others.

If the first three characters contain a number I'd like the cell to be blank.

Current Formula =IFERROR(VLOOKUP($E2,Sheet1!$A$2:$C$10000,2,FALSE),"")
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

exceldevs

Active Member
Joined
Aug 4, 2013
Messages
255
Hi Red,

Try this.
=IF(SUM(--(ISNUMBER(VALUE(MID($E2,{1,2,3},1)))))=0,IFERROR(VLOOKUP($E2,Sheet1!$A$2:$C$10000,2,FALSE),""),"")
 

Redwolfx

Well-known Member
Joined
Feb 22, 2013
Messages
1,161
Thanks for the quick response. I've been working on it as well.

I actually got it working slightly differently

=IF(ISNUMBER(LOOKUP(99^99,SEARCH({0,1,2,3,4,5,6,7,8,9},E2))),"",IFERROR(VLOOKUP($E2,Sheet1!$A$2:$C$10000,2,FALSE),""))
 

Watch MrExcel Video

Forum statistics

Threads
1,123,492
Messages
5,601,994
Members
414,490
Latest member
Rip181

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