Excel question from Bob (i'm running out of private msg)Prev TopicNext Topic

• nibor200

  

  United States
  Member #203,639
  January 11, 2020
  19 Posts
  Offline
  Feb 13, 2020, 12:03 pm

  ▲▼

  Bob,

  1.  We need to handle this request and any future others by open forum topics as I'm a Standard level member here so my private msg capability is limited and then I'll not be able to read any more of them.

  2. Regarding your recent request ...

  "Could you make
  a formula for the 4 digit that will
  match 3 numbers?
  We tried to change
  the 2 digit formula that you gave us
  for the 3 digit...

  
  =IF(LEFT(TEXT(A1,"000"),2)=TEXT($K$1,"00"),"TRUE "&TEXT(A1,"000"),IF(LEFT(TEXT(A1,"000"),1)&RIGHT(TEXT(A1,"000"),1)=TEXT($K$1,"00"),"TRUE "&TEXT(A1,"000"),IF(RIGHT(TEXT(A1,"000"),2)=TEXT($K$1,"00"),"TRUE "&TEXT(A1,"000"),"")))
  This will match 2 digit value in cell K1 with the leftmost 2 digits, or the rightmost 2 digits, or the leftmost single digit and the right most single

  digit of the 3 digit number in any of the A cells that have matches.

  
  It would be about the same butt A ROW would have 4 numbers.
  Bob in Michigan"

   

  Once again, I'll need more clarification as I'm not following how you want 4 digits out of a 3 digit number???

  I'm probably reading it wrong so please help with an actual example of what you want,

  Thanks,

  Ed S.
• Bob

  

  Michigan
  United States
  Member #59
  November 10, 2000
  417 Posts
  Offline
  Feb 13, 2020, 1:33 pm

  ▲▼

  Hi Ed,
  Bob -- You helped me out with excel
  a while back..Need help again if you have the time.
   .Right now the formula is
  for a 3 digit number finding 2 numbers.
  Butt we tried to change the formula to find 3 digits
  in a 4 number system...butt cant get it to work..
  was wondering if you could look at it and give us help??
  Hope we explained it right.

  the single formula below is expecting your data
  to be in column A and the digit
  you are looking for, to be in cell K1
  In cell b1 paste the following formula
  find 2 digit in A1---
  =IF(LEFT(TEXT(A1,"000"),2)=TEXT($K$1,"00"),"TRUE "&TEXT(A1,"000"),IF(LEFT(TEXT(A1,"000"),1)&RIGHT(TEXT(A1,"000"),1)=TEXT($K$1,"00"),"TRUE "&TEXT(A1,"000"),IF(RIGHT(TEXT(A1,"000"),2)=TEXT($K$1,"00"),"TRUE "&TEXT(A1,"000"),"")))

   

  Thanks again Ed and sorry about that..
  Bob
• nibor200

  

  United States
  Member #203,639
  January 11, 2020
  19 Posts
  Offline
  Feb 13, 2020, 2:30 pm

  ▲▼

  Hey Bob,

  Could you reply with a specific numerical example of what you mean by find a 3 digits in a 4 number system?

  Do you mean Pick4?

  thanks,

  Ed S.
• Bob

  

  Michigan
  United States
  Member #59
  November 10, 2000
  417 Posts
  Offline
  Feb 13, 2020, 3:50 pm

  ▲▼

  Yes PICK4 ...Sometimes it is a easy one to explain 

  and than a hard one to explain the right way...

  so yes it is a Pick 4 looking for 3 digits....
• Bob

  

  Michigan
  United States
  Member #59
  November 10, 2000
  417 Posts
  Offline
  Feb 15, 2020, 12:21 pm

  ▲▼

  anyone know how to change the EXCEL Formula????

   

  please do
• nibor200

  

  United States
  Member #203,639
  January 11, 2020
  19 Posts
  Offline
  Feb 15, 2020, 4:51 pm

  ▲▼

  Hey Bob,

  I didn't know this was a rush item :)

  Here is what I've come up with (pick4 makes it more involved for a formula approach)

  put the 3 digit number in cell K1 and the following formula in cell B1 and copy and paste as far down in the B column as your data in the A column goes.

  =IF(LEFT(TEXT(A2,"0000"),3)=TEXT($K$1,"000"),"TRUE "&TEXT(A2,"0000"),IF(LEFT(TEXT(A2,"0000"),1)&RIGHT(TEXT(A2,"0000"),2)=TEXT($K$1,"000"),"TRUE "&TEXT(A2,"0000"),IF(LEFT(TEXT(A2,"0000"),2)&RIGHT(TEXT(A2,"0000"),1)=TEXT($K$1,"000"),"TRUE "&TEXT(A2,"0000"),IF(RIGHT(TEXT(A2,"0000"),3)=TEXT($K$1,"000"),"TRUE "&TEXT(A2,"0000"),""))))

  This will match 3 digit values in cell K1 with the leftmost 3 digits, or the rightmost 3 digits, or the leftmost single digit and the right most 2 digits, or the leftmost 2 digits and the right most single digit of the 4 digit number in any of the A cells that have matches.

  In other words if "123" is in cell K1 it will match A cell values of 1234, 5123,1623 and 1273, etc.

  Let me know if this is not what you wanted,

  Ed S.
• Bob

  

  Michigan
  United States
  Member #59
  November 10, 2000
  417 Posts
  Offline
  Feb 15, 2020, 6:12 pm

  ▲▼

  Hi ED,

  not working right...

  not listing right... anyway wanted it

  to work  like

  all 389

  2398----2398

  3879----3879

  3890----3890

  thank you ....I know it is lots of work..

  butt Thank you ahead of time..

  bob
• Bob

  

  Michigan
  United States
  Member #59
  November 10, 2000
  417 Posts
  Offline
  Feb 15, 2020, 6:51 pm

  ▲▼

  Hi ED,

  Thank you it is working  super...It was my fault

  that it wasnt working right ...OK got it going 

  thanks to you...

   

  bob