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
ADRIANA PACCOUNTING ASSISTANTAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
What if the number was "151"?
0
ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
HI MARTIN ! THANKS FOR YOU INQUIRY COULD BE TOO
WILL BE  AN LIST OF ANY NUMBERS
0
Rob HensonFinance AnalystCommented:
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
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Get Your eBook
ShumsDistinguished Expert - 2017Commented:
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",""))))
0
Martin LissOlder than dirtCommented:
Try this
29010301.xlsm
0
ShumsDistinguished Expert - 2017Commented:
@ Rob, Position is not always 2 for substitute formula
0
ShumsDistinguished Expert - 2017Commented:
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",""))))
0
Rob HensonFinance AnalystCommented:
I haven't specified position in the formula.

=SUBSTITUTE(Text,Old Text, New text,Occurrence)
=SUBSTITUTE(B7,Number,"")    Occurence parameter omitted.
0
ShumsDistinguished Expert - 2017Commented:
@ Rob what you get for 018?
0
Rob HensonFinance AnalystCommented:
018 gives result of 01

What do you get for 101 or 050, or other combinations of 0*0 and 1*1?
0
ShumsDistinguished Expert - 2017Commented:
Check attached....
CERO-OR-ONE_v1.xlsx
0
Rob HensonFinance AnalystCommented:
So we need Adriana to confirm action when the number contains 1 and 0 and multiple occurrences of either that are not adjacent.
0
ADRIANA PACCOUNTING ASSISTANTAuthor 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.....
0
Rob HensonFinance AnalystCommented:
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
0
Martin LissOlder than dirtCommented:
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.
0
ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
Right Rob you are the best opcion because have to be like that
 
018  should give 01 and so on
0
ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
I mean option
0
ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
Rob Great ! Job ! That's  what i need !
0
Martin LissOlder than dirtCommented:
Try this update.
29010301a.xlsm
1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Rob HensonFinance AnalystCommented:
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???
0
ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
Martin is working now !!!
excuse me because already I select the  ROB answer as the best
but i like the your approach!  Too !!
0
ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
Shums I like your fast response ! and send by example
great job!
0
ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
Rob is because some inventory ajustments  have to be made
0
ShumsDistinguished Expert - 2017Commented:
Martin's Solution is short and best.
0
ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
Martin Great Job !!
0
Martin LissOlder than dirtCommented:
Thank you Shums.
0
ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
Thnks very much for you time and  help All of you are Great Experts !
it help me a lot !
0
ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
GREAT EXPERTS !! THE BEST !
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.