extract ending 10 digits in a cell only after underscore not less or more and do not include underscore and after it characters

[sapxl]

Dear friends,

my query is - After underscore (_) only 10 digits and not accept less than or more than 10 digits entered, it should give alert... Is there any formula combination like- IF LEN,RIGHT function to do this work.. Appreciate your help…

From the above examples, it is taking the characters _ and other characters along with the digits to complete 10 digits display… in right formula, is it possible to restrict before _(underscore) only allow 10 digits not less or more than 10 digits….

eg: meenr_product004_8403465 ---- last numbers 10 digits to be allowed to enter after the underscore if less than 10 digits then it should give me error message like ïncomplete number etc..


I'm trying with the below functions but did not get any result so far...

=RIGHT(D9,10)
=LEN(E8)
=IF(D5 = "_", RIGHT(D5,10), "NUMBER NOT VALID")
=(RIGHT(D5, FIND("_",D5)))
=LEN(RIGHT(D5, FIND("_",D5)))


Please see the screenshot in attachments...

Thanks in advance.

 

[CA_Punit]

=IF(ISNUMBER(SEARCH("_",RIGHT(D4,10))),"Incomplete Number",RIGHT(D4,10))

D4 has Values

 

[sapxl]

Hi Punit,

Thanks a ton, this code is working as expected, but it is taking after the 10 digits of underscore, it is taking further numbers entries. is it possible to restrict the number of characters to 10 digits only after the underscore... kindly check the screenshot..

 

[Fluff]

How about

+Fluff New.xlsm
	D	E
4	123_456_123456789	Check
5	123_456_1234567891	1234567891
6	123_456_12345678921	Check
Main

Cell Formulas
Range	Formula
E4:E6	E4	=IF(LEN(TRIM(RIGHT(SUBSTITUTE(D4,"_",REPT(" ",100)),100)))<>10,"Check",RIGHT(D4,10))

 

[sapxl]

Hi Fluff,
Great!!! It is working more than expected... Thank you very much for your help.

 

[sapxl]

Fluff your solution worked... infact thanks to both (Fluff and punith)...

 

[Fluff]

Glad we could help & thanks for the feedback.