LinkBack URL
About LinkBacks
I have codes on cell a1 that look as 100.000.01
I need a formula to cell b1 that will identify any
code that is not in this format e.g. 100.0000.01
I have codes on cell a1 that look as 100.000.01
I need a formula to cell b1 that will identify any
code that is not in this format e.g. 100.0000.01
try
=AND(SUMPRODUCT((IFERROR(FIND(".",MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0))*ROW(INDIRECT("1:"&LEN(A1))))=12,LEN(A1)=10)
array entered!
Last edited by hemesh; 05-10-2014 at 05:22 AM.
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
WANT TO SAY THANKS, HIT ADD REPUTATION (*) AT THE BOTTOM LEFT CORNER OF THE POST
More we learn about excel, more it shows us, how less we know about it.
for chemistry
https://www.youtube.com/c/chemistrybyshivaansh
Another way around....
Assuming that you have 100.000.01 in A1, then in B1 try this.....
Please Login or Register to view this content.
Last edited by sktneer; 05-10-2014 at 05:57 AM.
Regards
sktneer
Treat people the way you want to be treated. Talk to people the way you want to be talked to.
Respect is earned NOT given.
Hi.Originally Posted by makinmomb
What do you mean precisely by "this format"? You mean that it should always be:
3 numbers, a dot, 3 numbers, a dot, 2 numbers?
Regards
I think to check length is necessary as user may add more numbers after second "." entryAnother way around....
Assuming that you have 100.000.01 in A1, then in B1 try this.....
Please Login or Register to view this content.
@hemesh
Thanks for the pointing this out. I have edited the formula.
3 numbers, a dot, 3 numbers, a dot, 2 numbers?
Yes always in this format
Thanks Hemesh , I chose to take that of Sktneer since it is non array ,
While I have collected your array into my collection
That's good to here !
Below is the non array solution for Your COLLECTION
=AND(SUMPRODUCT(--(ISNUMBER(FIND(".",MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))))*ROW(INDIRECT("1:"&LEN(A1))))=12,LEN(A1)=10)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks