+ Reply to Thread
Results 1 to 4 of 4

Finding values in a column with a certain pattern

  1. 10-06-2021, 04:47 AM #1
    chandramouliarun
    chandramouliarun is offline
    Forum Contributor
    Join Date
    10-18-2016
    Location
    Chicago, USA
    MS-Off Ver
    Office 365
    Posts
    220

    Red face Finding values in a column with a certain pattern

    Hi All,

    I am trying to find values in a column that have 5 characters with the first one being an alphabet followed by four numbers ( for example H3412).. Those values have a matching value of Yes .. Can you please help me here....Please find attached the file with the input column charge code and the expected output column Matching which has the value of Yes for values that have 5 characters with the first one being an alphabet followed by four numbers and No for others..




    Thanks,
    Arun
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 10-06-2021, 04:56 AM #2
    AliGW
    AliGW is offline
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    80,830

    Re: Finding values in a column with a certain pattern

    One way:

    =IF(AND(LEN(A2)=5,NOT(ISNUMBER(--LEFT(A2,1))),ISNUMBER(--RIGHT(A2,4))),"Yes","No")
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  3. 10-06-2021, 05:06 AM #3
    Bo_Ry
    Bo_Ry is offline
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Finding values in a column with a certain pattern

    Please try

    =IF((A2>"a")*(RIGHT(A2,4)<"a")*(LEN(A2)=5),"Yes","No")
    Linkedin/Bo-Rydobon
    Youtube.com/XcWizard
    Facebook.com/XcWizard
    Reply With QuoteRegister To Reply
  4. 01-10-2022, 08:26 AM #4
    chandramouliarun
    chandramouliarun is offline
    Forum Contributor
    Join Date
    10-18-2016
    Location
    Chicago, USA
    MS-Off Ver
    Office 365
    Posts
    220

    Re: Finding values in a column with a certain pattern

    Thanks @ AliGw and @ Bo_Ry...Really appreciate your help!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Pattern Finding for Pricing - Formula
    By jmcconville18 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-19-2015, 03:45 PM
  2. Finding data for a pattern
    By sara_b in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-21-2014, 12:25 PM
  3. [SOLVED] Finding a pattern
    By ChiknNuggets in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-21-2013, 10:26 PM
  4. Finding all occurrences of a pattern within a column
    By matcapir in forum Excel Programming / VBA / Macros
    Replies: 16
    Last Post: 10-05-2011, 11:16 AM
  5. Finding the pattern
    By sssss2 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-27-2009, 04:47 PM
  6. Pattern Finding in Excel
    By deepslp in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 03-18-2009, 06:45 PM
  7. Pattern Finding in Excel
    By deepslp in forum Excel General
    Replies: 4
    Last Post: 03-13-2009, 02:47 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1