• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

letter O or number 0

dgk

Member
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
 
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
 
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.
 
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.
 
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),""))
 
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
 
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.
 

Attachments

  • Find zeros and letter O.xlsm
    19 KB · Views: 5
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)
 
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
 
Back
Top