Hi, Could you please help with a formula to extract "P4/*"up to the comma "," from the following examples:
Data --------------------------------------- Output
P4/3H6, ----------------------------------- P4/3H6
G6/3H4,GEN/18A12,P4/3H4, ------------ P4/3H4
P4/2C1.1,SLD/DIST, --------------------- P4/2C1.1
CEN/REVMET,P4/2C8, ------------------- P4/2C8
G8/3H3,L9/3H3,P4/3H3,R111/3H3, ---- P4/3H3
P4/3H15,S14/3H15, --------------------- P4/3H15
I have used MID and FIND in this formula: =MID(E4,FIND("P4/",E4)*1,8) to extract 8 characters, but the majority of the required strings are 6, some are 7 and a few are 8. My knowledge of Excel is not good enough to find the length of the P4/ string up to the comma and only extract it. I have searched and looked at some similar extraction formula but can not get them to work properly for me e.g. =MID(E4,FIND("P4/",E4)*1,(FIND(",",E4)-(FIND("P4/",E4)*1)))
Any help would be much appreciated
Data --------------------------------------- Output
P4/3H6, ----------------------------------- P4/3H6
G6/3H4,GEN/18A12,P4/3H4, ------------ P4/3H4
P4/2C1.1,SLD/DIST, --------------------- P4/2C1.1
CEN/REVMET,P4/2C8, ------------------- P4/2C8
G8/3H3,L9/3H3,P4/3H3,R111/3H3, ---- P4/3H3
P4/3H15,S14/3H15, --------------------- P4/3H15
I have used MID and FIND in this formula: =MID(E4,FIND("P4/",E4)*1,8) to extract 8 characters, but the majority of the required strings are 6, some are 7 and a few are 8. My knowledge of Excel is not good enough to find the length of the P4/ string up to the comma and only extract it. I have searched and looked at some similar extraction formula but can not get them to work properly for me e.g. =MID(E4,FIND("P4/",E4)*1,(FIND(",",E4)-(FIND("P4/",E4)*1)))
Any help would be much appreciated