Link to home
Start Free TrialLog in
Avatar of ADRIANA P
ADRIANA PFlag for United States of America

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
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

What if the number was "151"?
Avatar of ADRIANA P

ASKER

HI MARTIN ! THANKS FOR YOU INQUIRY COULD BE TOO
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
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",""))))
@ 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",""))))
SOLUTION
Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
@ 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?
Check attached....
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.
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.....
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
I TRY THE MARTIN  BUT WHEN I WRITE 056 I DON'T GET IT
If 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.
Right Rob you are the best opcion because have to be like that
 
018  should give 01 and so on
I mean option
Rob Great ! Job ! That's  what i need !
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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???
Martin is working now !!!
excuse me because already I select the  ROB answer as the best
but i like the your approach!  Too !!
Shums I like your fast response ! and send by example
great job!
Rob is because some inventory ajustments  have to be made
Martin's Solution is short and best.
Martin Great Job !!
Thank you Shums.
Thnks very much for you time and  help All of you are Great Experts !
it help me a lot !
GREAT EXPERTS !! THE BEST !