letter O or number 0

[dgk]

Hi
is there a way in excel to find if part of my text string is entered as letter "O" , or as number 0 ?

basically

there is a list of text strings that was done manually somebody copied information from an existing list and some strings were copied wrong instead of the letter O the number 0 was put in , and vice versa
is there a function to find out if a string has a letter O or a number 0 (its usually in the middle of the string)

Thanks a million

 

[vletm]

=find("0",Your_text)
=find("O",Your_text)

 

[Peter Bartholomew]

Another possibility is to use
= SUBSTITUTE(text,"0","O")
and simply correct the "D0G's breakfast" without even having to look at it.

 

[dgk]

Hi
I am trying the find approach since i do need to find those instances , but i noticed that when there is 2 or more instances with a zero or letter o in a string ,it only gives me the first position in the string where it was found
Any workaround for this ?
Thanks

 

[shrivallabha]

Are you expecting only alphabets? What defines O is correct and 0 is incorrect? It will be better if you could post few examples of data with manual demonstration so that it becomes clearer.

 

[Peter Bartholomew]

Using Excel 365, the location of "0"s might be returned using

= LET(
   k, SEQUENCE(1,LEN(text)),
   FILTER(k, MID(text,k,1)="0" ))

 

[vletm]

dgk
What do You expect to get, if Your original writing:
is there a way in excel to find if part of my text string is entered as letter "O" , or as number 0 ?
and now
You seems to ask something else.
Do You know that even Yourself?
Hint: copy & paste could do correct way too.

 

[dgk]

Hi
Please see attached sample file

 

[shrivallabha]

If you have Office 365 and access to functions (SEQUENCE,TEXTJOIN) then for Zeroes the formula would be:
=TEXTJOIN(",",TRUE,IF(MID($A2,SEQUENCE(99,1,1,1),1)="0",SEQUENCE(99,1,1,1),""))

For O Letter:
=TEXTJOIN(",",TRUE,IF(MID($A2,SEQUENCE(99,1,1,1),1)="O",SEQUENCE(99,1,1,1),""))

 

[vletm]

dgk
Modify Your A-column something to get those ...

 

[dgk]

My Version is office plus professional 2016
don't have these functions

 

[dgk]

Hi vletm
don't understand what you mean by modifying column a (the file that i attached is just an example)
i have more than 600 items in that column
need to find out which of the items the O is letter O and which is number 0
and need to find "all" occurrences in the string for both the letter and number
your first response worked great , but only finds the the first occurrence

Thanks again

 

[vletm]

dgk
Other version...
#1 Fill Your A-column as You would like to do in Sheet1 ... okay?
#2 Select Sheet2 ... okay?
#3 Select Sheet1 ... okay?
#4 If You would like to add/delete A-column data then repeat steps from #1 to #3 as many times as it'll need.

About Your but only finds the the first occurrence
That works as You asked.

 

[dgk]

Don't understand
want me to send my file ?
i need to this here cant send my file

 

[vletm]

dgk
Do You have my above file? Yes / No?

 

[dgk]

yes

 

[vletm]

dgk
#1 Make sure that it is from #13 reply.
#2 Open it
#3 Select Sheet1

 

[dgk]

yes
i did
i dont see there any function or any macro

just see that the zeros where marked with a certain color and the O with an other color (manually)

 

[vletm]

dgk
... did I ask to do something else?
... continue ..
#4 copy Your ~600 items from other file as You would normally do
#5 select cell A2 ( from Sheet1 )
#6 do paste

 

[Peter Bartholomew]

Filled down formula (I struggle once deprived of dynamic arrays)

 

[vletm]

dgk
#7 Select Sheet2
#8 Select Sheet1 to get results

 

[dgk]

david will respond a little later

 

[dgk]

Hi
Thanks vletm and Peter
Both solution's worked perfectly

you are great