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



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,


Ed S.


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..


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?


Ed S.


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....


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


please do


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.


Hi ED,

not working right...

not listing right... anyway wanted it

to work  like

all 389




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

butt Thank you ahead of time..



Hi ED,

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

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

thanks to you...



TopEnd of thread (1 page)

Welcome Guest

Your last visit: Sat, Dec 5, 2020, 4:20 pm

Log In

Log InCancel

Forgot your username?

Forgot your password?