Search for P followed by four numbers

[wanjaf]

Hi everyone,

I would like to search a cell for a string "p" followed by four numbers. Eg. P0400

Any ideas?
Thanks,

[:) Sixthsense :)]

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[Sam Capricci]

Searching can be done many ways. One way would be to use filters, then click on text filters and you can select "begins with..." or "Contains..." or some other parameter. It is difficult to give you a more detailed answer without more information or as 6th sense noted - a sample file with before and after.
If you just need a formula then a left formula would work but you don't say if the P is always on the far left side of the cell or the lengths are consistent where a P would be found.

[wanjaf]

Sorry. Here you go!

Thank you so much for your time and effort!

[samba_ravi]

see the attached file

[AlKey]

Maybe this

=IF(LEN("P"&SUBSTITUTE(TRIM(RIGHT(SUBSTITUTE(A4,"P",REPT(" ",25)),25)),".",""))=5,"Yes","No")

[wanjaf]

Brilliant, nflsales! Thank you so much. But isn't it weird that if I replace "P0500" in cell A4 to "P1500" the function returns no Yes nor No (= error)?

Alkey, your solution works too, thank you! However when I add some characters behind the P-code (E.g. "P0111 some more text") the function returns "No".

[AlKey]

Dear wanjaf. You can always count on getting more accurate results if you provide more accurate complete requirements. Unfortunately, this wasn't the case.

[AlKey]

See if this one will work better

=IF(MID(A4,LOOKUP(25^25,MIN(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},A4),""))-1),1)="P","Yes","No")

Row\Col	A	B
3	Title	Title contains P-code?
4	TPA Wire-harness - wrong assembly - DTC P0500	Yes
5	Front Pass Side rear airbag sensor malfunction (DTC B1630)	No
6	Automatic gearbox malfunction DTC:P2714.	Yes
7	Tire pressure monitor bulb functional [ Poor ] (C2121)	No
8	Automatic gearbox malfunction DTC:P2714POII	Yes
9	Front Pass Side rear airbag sensor malfunction (DTC :P0156POPYYY	Yes

[István Hirsch]

Try this formula in B2, confirm with Ctrl – Shift – Enter, not just Enter:

Please Login or Register  to view this content.

[wanjaf]

Thank you, Alkey! Your formula works perfectly. Sorry I was not clear from the beginning.

Thank you as well, István Hirsch, your formula works too when I confirm with Ctrl – Shift – Enter!

[István Hirsch]

Please check. At me:

Formula in #9 says „YES” for P1 or P21 etc., not only for P1234 and says „NO” for P1234xx.

Formula in #5 says „YES” for P1234567 and says nothing for xxxP1xxxP1234 or xxx2xxxP1234

[AlKey]

You're welcome and thank you for the feedback!

Please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools -> Mark thread as Solved).

[István Hirsch]

You're welcome and thank you for the feedback!

Please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools -> Mark thread as Solved).

Please check:

Your formula in #9 does not check the number of digits following „P” so it gives false positive for any number of digits following „P”, for example for P1, P12, P123, P1234567 etc.
It gives false negative if a P1234 structure is preceded by any number, for example xx2xxP1234.