• 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.

Text and numbers in cell

Y

yaelcohen

Member
Hello Expert

I appreciate for your effort to answer my question

I have a data, which built from more than 1000 cells of many subjects, while part of it are cells with ID numbers. The ID is sometimes written as I"D or I.D or other variation of it. Next to it, in the same cell there is the ID number itself.

Next to each line that has an ID in the data I want to write "D"
For this I write the next formula
=IF(AND(SEARCH("I",C5,1),SEARCH("D",C5,1),OR(SEARCH("0",C5,1),SEARCH("1",C5,1)....))=TRUE,"D")

C5 = ID 03583011, ....= All the numbers from 0-9

The answer I got is #value and I do not know why

Can you pls help in this issue

Thanks

Yael
 
If You write to cell like = ID C5, then Excel give an message like 'not correct formula'.
If You write to cell like = IDC5, then Excel shows cells ID12345 value.
If You write to cell like 'ID C5, then Excel knows that it is 'text'.
If You write to cell like = "ID " & C5, then Excel knows that it is a formula.
... and so on. Ideas?
 
Following formula can handle ID variations of type ID,I[Any one Character]D and is not case sensitive. Check whether it helps:
=IF(IFERROR(SEARCH("I?D",C5)>0,SEARCH("ID",C5))>0,"D","")
I have not added any check for Numeric part which you probably know how to do.
 
You must log in or register to reply here.
Back
Top