About
Pricing
Community
Teams
Start Free Trial
Log in
ADRIANA P
asked on
3/20/2017
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
Microsoft Excel
Microsoft Office
28
2
Last Comment
ADRIANA P
8/22/2022 - Mon
Martin Liss
3/20/2017
What if the number was "151"?
ADRIANA P
3/20/2017
ASKER
HI MARTIN ! THANKS FOR YOU INQUIRY COULD BE TOO
WILL BE AN LIST OF ANY NUMBERS
Rob Henson
3/20/2017
Adriana,
this formula will replace any occurrence of anything other than 1 or 0 with a blank:
=SUBSTITUTE(SUBSTITUTE(SUB
STITUTE(SU
BSTITUTE(S
UBSTITUTE(
SUBSTITUTE
(SUBSTITUT
E(SUBSTITU
TE(B6,2,""
),3,""),4,
""),5,""),
6,""),7,""
),8,""),9,
"")
Thanks
Rob
Your help has saved me hundreds of hours of internet surfing.
fblack61
Shums Faruk
3/20/2017
try below:
=IF(ISNUMBER(SEARCH("0*",B
6)),"0",IF
(ISNUMBER(
SEARCH("0*
",B6)),"00
",IF(ISNUM
BER(SEARCH
("11*",B6)
),"11",IF(
ISNUMBER(S
EARCH("1*"
,B6)),"1",
""))))
Martin Liss
3/20/2017
Try this
29010301.xlsm
Shums Faruk
3/20/2017
@ Rob, Position is not always 2 for substitute formula
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Shums Faruk
3/20/2017
Sorry Corrected one:
=IF(ISNUMBER(SEARCH("*00*"
,B6)),"00"
,IF(ISNUMB
ER(SEARCH(
"*0*",B6))
,"0",IF(IS
NUMBER(SEA
RCH("*11*"
,B6)),"11"
,IF(ISNUMB
ER(SEARCH(
"*1*",B6))
,"1","")))
)
SOLUTION
Rob Henson
3/20/2017
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Shums Faruk
3/20/2017
@ Rob what you get for 018?
Rob Henson
3/20/2017
018 gives result of 01
What do you get for 101 or 050, or other combinations of 0*0 and 1*1?
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Shums Faruk
3/20/2017
Check attached....
CERO-OR-ONE_v1.xlsx
Rob Henson
3/20/2017
So we need Adriana to confirm action when the number contains 1 and 0 and multiple occurrences of either that are not adjacent.
ADRIANA P
3/20/2017
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.....
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Rob Henson
3/20/2017
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 Liss
3/20/2017
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 P
3/20/2017
ASKER
Right Rob you are the best opcion because have to be like that
018 should give 01 and so on
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
ADRIANA P
3/20/2017
ASKER
I mean option
ADRIANA P
3/20/2017
ASKER
Rob Great ! Job ! That's what i need !
ASKER CERTIFIED SOLUTION
Martin Liss
3/20/2017
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Rob Henson
3/20/2017
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???
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ADRIANA P
3/20/2017
ASKER
Martin is working now !!!
excuse me because already I select the ROB answer as the best
but i like the your approach! Too !!
ADRIANA P
3/20/2017
ASKER
Shums I like your fast response ! and send by example
great job!
ADRIANA P
3/20/2017
ASKER
Rob is because some inventory ajustments have to be made
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Shums Faruk
3/20/2017
Martin's Solution is short and best.
ADRIANA P
3/20/2017
ASKER
Martin Great Job !!
Martin Liss
3/20/2017
Thank you Shums.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ADRIANA P
3/21/2017
ASKER
Thnks very much for you time and help All of you are Great Experts !
it help me a lot !
ADRIANA P
3/22/2017
ASKER
GREAT EXPERTS !! THE BEST !