Excel Conditional Formating to find numbers a cell with text

0 votes

I looked and looked but couldn't come up with a satisfactory response. I've got a list of films. Some have the year of release, while others do not.

Is there a way to highlight the cells that contain numbers using conditional formatting. usually have four digits. The numerals are never at the start or end of the name; instead, they are always scattered throughout. Additionally, they are never left alone. Because "You may not utilise unions, intersections, or array constants for Conditional Formatting Criteria," I discovered a few examples that don't function. whatever that might imply.

Here is a sample list:

Airborne
Airplane II - The Sequel 1982
Alien 3 1992
Alien 1979
Alien Contamination
Alien Covenant 2017
Alien Resurrection 1997
Bad Santa 2

The second, third, fourth, sixth, seventh, and eighth would be what I would anticipate being featured. If it could only find the years and ignore the single digits, that would be ideal, but it's also fine if it can't.

Please refrain from utilising visual basic or macros. I am using Excel 2007 because it was purchased; I am not receiving a subscription for something I already own. I truly don't want to grasp that things because I don't.

Jan 10 in Others by Kithuzzz
• 28,900 points
28 views

1 answer to this question.

0 votes

Try this:

=OR(ISNUMBER(-MID(SUBSTITUTE(A1," ","~")&"~",seq,4)))

where seq is a defined name that refers to:

=ROW(INDEX(Sheet1!$A:$A,1):INDEX(Sheet1!$A:$A,255))

seq merely returns an array of numbers {1..255}

answered Jan 10 by narikkadan
• 53,520 points

Related Questions In Others

0 votes
1 answer

Excel VBA : HOW TO PRINT THE TEXT IN A CELL (like Wrap Text)

Use a LineFeed character to create a ...READ MORE

answered Oct 27, 2022 in Others by narikkadan
• 53,520 points
195 views
0 votes
1 answer

Text with a dash in the cell but not in the formula bar (Excel)

What you have got here is called ...READ MORE

answered Nov 13, 2022 in Others by narikkadan
• 53,520 points
99 views
0 votes
1 answer

Excel formula to replace or stubstitute only text that starts with a certain letter

You're requesting a "formula." Normally, you would ...READ MORE

answered Nov 21, 2022 in Others by narikkadan
• 53,520 points
86 views
0 votes
1 answer

Create a hyperlink to a sheet with same name as the value in the selected cell in Excel through VBA

Credit to Spectral Instance who found the ...READ MORE

answered 2 days ago in Others by narikkadan
• 53,520 points
28 views
0 votes
1 answer

Excel function for divide or split number to maximum possible equal parts

The underlying math for this is as ...READ MORE

answered Oct 17, 2022 in Others by narikkadan
• 53,520 points
246 views
0 votes
1 answer

Highlight active row/column in Excel without using VBA?

Create two formula-based rules: =ROW()=CELL("row") =COLUMN( ...READ MORE

answered Oct 27, 2022 in Others by narikkadan
• 53,520 points
449 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
533 views
0 votes
1 answer

ImportError: openpyxl is required for loading excel format files

Forget the PsychoPy complications for the time ...READ MORE

answered Oct 3, 2018 in Python by Priyaj
• 58,100 points
482 views
0 votes
1 answer

Append same text to every cell in a column in Excel

Solution All your data is in column A ...READ MORE

answered Oct 17, 2022 in Others by narikkadan
• 53,520 points
173 views
0 votes
1 answer

How to insert a picture into Excel at a specified cell position with VBA

Try this: With xlApp.ActiveSheet.Pictures.Insert(PicPath) With ...READ MORE

answered Oct 24, 2022 in Others by narikkadan
• 53,520 points
460 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP