ADRIANA P

asked on

# IDENTIFY THE CERO OR ONE OR BOTH IN AN NUMBER ??

IF A HAVE AN NUMBER LIKE

560 I NEED WRITHE THE 0 IN THE ADJACENT COLUMN

EXAMPLE HERE

CERO-OR-ONE.xlsx

560 I NEED WRITHE THE 0 IN THE ADJACENT COLUMN

EXAMPLE HERE

CERO-OR-ONE.xlsx

What if the number was "151"?

ASKER

HI MARTIN ! THANKS FOR YOU INQUIRY COULD BE TOO

WILL BE AN LIST OF ANY NUMBERS

WILL BE AN LIST OF ANY NUMBERS

Adriana,

this formula will replace any occurrence of anything other than 1 or 0 with a blank:

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B6,2,""),3,""),4,""),5,""),6,""),7,""),8,""),9,"")

Thanks

Rob

this formula will replace any occurrence of anything other than 1 or 0 with a blank:

=SUBSTITUTE(SUBSTITUTE(SUB

Thanks

Rob

try below:

=IF(ISNUMBER(SEARCH("0*",B6)),"0",IF(ISNUMBER(SEARCH("0*",B6)),"00",IF(ISNUMBER(SEARCH("11*",B6)),"11",IF(ISNUMBER(SEARCH("1*",B6)),"1",""))))

=IF(ISNUMBER(SEARCH("0*",B

Try this

29010301.xlsm

29010301.xlsm

@ Rob, Position is not always 2 for substitute formula

Sorry Corrected one:

=IF(ISNUMBER(SEARCH("*00*",B6)),"00",IF(ISNUMBER(SEARCH("*0*",B6)),"0",IF(ISNUMBER(SEARCH("*11*",B6)),"11",IF(ISNUMBER(SEARCH("*1*",B6)),"1",""))))

=IF(ISNUMBER(SEARCH("*00*"

SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

@ Rob what you get for 018?

018 gives result of 01

What do you get for 101 or 050, or other combinations of 0*0 and 1*1?

What do you get for 101 or 050, or other combinations of 0*0 and 1*1?

Check attached....

CERO-OR-ONE_v1.xlsx

CERO-OR-ONE_v1.xlsx

So we need Adriana to confirm action when the number contains 1 and 0 and multiple occurrences of either that are not adjacent.

ASKER

GENTLEMEN ALL OF YOU ARE DOING GREAT JOB!!

Martin Liss, Rob Henson, Shums THANKS !!

I TRY THE MARTIN BUT WHEN I WRITE 056 I DON'T GET IT

SAME FIRST TIME WITH Shums

BUT ROB APPROACH WORK FROM THE from the beginning.....

Martin Liss, Rob Henson, Shums THANKS !!

I TRY THE MARTIN BUT WHEN I WRITE 056 I DON'T GET IT

SAME FIRST TIME WITH Shums

BUT ROB APPROACH WORK FROM THE from the beginning.....

Can you confirm that the result from number 018 should be 01? Your sample file showed it with only 0.

Likewise, please confirm results when number contains 0 and 1 or multiple occurrences which are not adjacent, like Martin's first question with 151.

Thanks

Rob

Likewise, please confirm results when number contains 0 and 1 or multiple occurrences which are not adjacent, like Martin's first question with 151.

Thanks

Rob

I TRY THE MARTIN BUT WHEN I WRITE 056 I DON'T GET ITIf you format cell B19 like cell B18 and then you type 056 in cell B19 you will get 0 in cell C19 if you copy the formula from cell C18.

ASKER

Right Rob you are the best opcion because have to be like that

018 should give 01 and so on

018 should give 01 and so on

ASKER

I mean option

ASKER

Rob Great ! Job ! That's what i need !

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Glad to be of assistance! Please note it will only work for occurrences of 0 or 1. If you want it to be flexible for any other combinations it will have to be adjusted.

Can I ask why you need it anyway???

Can I ask why you need it anyway???

ASKER

Martin is working now !!!

excuse me because already I select the ROB answer as the best

but i like the your approach! Too !!

excuse me because already I select the ROB answer as the best

but i like the your approach! Too !!

ASKER

Shums I like your fast response ! and send by example

great job!

great job!

ASKER

Rob is because some inventory ajustments have to be made

Martin's Solution is short and best.

ASKER

Martin Great Job !!

Thank you Shums.

ASKER

Thnks very much for you time and help All of you are Great Experts !

it help me a lot !

it help me a lot !

ASKER

GREAT EXPERTS !! THE BEST !