We help IT Professionals succeed at work.

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

ADRIANA P
ADRIANA P asked
on
29 Comments
2 Solutions
170 Views
Last Modified: 2017-03-22
IF A HAVE AN NUMBER LIKE
560 I NEED WRITHE THE 0 IN THE ADJACENT COLUMN

EXAMPLE HERE
CERO-OR-ONE.xlsx
Comment
Watch Question

View Solutions Only

Martin LissProtect yourself and your loved ones. Stay home for the holidays.
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
What if the number was "151"?
ADRIANA PACCOUNTING ASSISTANT

Author

Commented:
HI MARTIN ! THANKS FOR YOU INQUIRY COULD BE TOO
WILL BE  AN LIST OF ANY NUMBERS
Rob HensonFinance Analyst
CERTIFIED EXPERT

Commented:
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
ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
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",""))))
Martin LissProtect yourself and your loved ones. Stay home for the holidays.
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Try this
29010301.xlsm
ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
@ Rob, Position is not always 2 for substitute formula
ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
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",""))))
Rob HensonFinance Analyst
CERTIFIED EXPERT
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
@ Rob what you get for 018?
Rob HensonFinance Analyst
CERTIFIED EXPERT

Commented:
018 gives result of 01

What do you get for 101 or 050, or other combinations of 0*0 and 1*1?
ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
Check attached....
CERO-OR-ONE_v1.xlsx
Rob HensonFinance Analyst
CERTIFIED EXPERT

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

Author

Commented:
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.....
Rob HensonFinance Analyst
CERTIFIED EXPERT

Commented:
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
Martin LissProtect yourself and your loved ones. Stay home for the holidays.
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
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.
ADRIANA PACCOUNTING ASSISTANT

Author

Commented:
Right Rob you are the best opcion because have to be like that
 
018  should give 01 and so on
ADRIANA PACCOUNTING ASSISTANT

Author

Commented:
I mean option
ADRIANA PACCOUNTING ASSISTANT

Author

Commented:
Rob Great ! Job ! That's  what i need !
Protect yourself and your loved ones. Stay home for the holidays.
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
Rob HensonFinance Analyst
CERTIFIED EXPERT

Commented:
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???
ADRIANA PACCOUNTING ASSISTANT

Author

Commented:
Martin is working now !!!
excuse me because already I select the  ROB answer as the best
but i like the your approach!  Too !!
ADRIANA PACCOUNTING ASSISTANT

Author

Commented:
Shums I like your fast response ! and send by example
great job!
ADRIANA PACCOUNTING ASSISTANT

Author

Commented:
Rob is because some inventory ajustments  have to be made
ShumsManaging Director/Excel VBA Developer
CERTIFIED EXPERT
Distinguished Expert 2018

Commented:
Martin's Solution is short and best.
ADRIANA PACCOUNTING ASSISTANT

Author

Commented:
Martin Great Job !!
Martin LissProtect yourself and your loved ones. Stay home for the holidays.
CERTIFIED EXPERT
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
Thank you Shums.
ADRIANA PACCOUNTING ASSISTANT

Author

Commented:
Thnks very much for you time and  help All of you are Great Experts !
it help me a lot !
ADRIANA PACCOUNTING ASSISTANT

Author

Commented:
GREAT EXPERTS !! THE BEST !

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions