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, 2023 in Others by Kithuzzz
• 38,010 points
294 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, 2023 by narikkadan
• 63,420 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
• 63,420 points
735 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
• 63,420 points
1,713 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
• 63,420 points
249 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 Feb 6, 2023 in Others by narikkadan
• 63,420 points
559 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
• 63,420 points
1,907 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
• 63,420 points
2,407 views
0 votes
1 answer

How to turn a number recognized as string to number

Try this: =VALUE(SUBSTITUTE(A1," ", "")) Or select cells with ...READ MORE

answered Mar 30, 2023 in Others by narikkadan
• 63,420 points
232 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
876 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
• 63,420 points
3,305 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
• 63,420 points
1,930 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