Extract Text and Number

[27458]

The following data in column "A". I need to be able to search each line and extract ANYTHING that begins with "H" and then all the characters until it reaches the first semicolon to the right. Example, first line would return H1/5, second line would return HP/7, third H1/11, etc. etc. The result would need to post to the adjacent cell. Thanks.

TB/10;MS/7;AS/6;H1/5;A3/4
TB/12;MS/7;AS/6;HP/7;C8/4;A4/1
TB/7;MS/7;AS/6;H1/11
MS/7;AS/6;M2/12;M0/5;C4/5;H3/10
TB/25;MS/7;AS/6;H1/5;A4/1
TB/12;MS/7;AS/6;C4/9;H6/3;A3/4
MS/7;AS/6;H1/10;A4/3
TB/6;H1/8;C8/6;AS/6;MS/7
TB/16;MS/7;AS/6;H1/8;A3/8;A4/6
MS/7;AS/6;H1/10;TD/1

 

[sanrv1f]

try this

=LEFT(RIGHT(A1,LEN(A1)-SEARCH("H",A1)+1),SEARCH(";",RIGHT(A1,LEN(A1)-SEARCH("H",A1)+1))-1)

 

[27458]

Found one gotcha. The code may be at the end and not have the semicolon. Otherwise it worked great.

MS/7;AS/6;M2/12;M0/5;C4/5;H3/10

 

[sanrv1f]

done

=IF(ISERROR(SEARCH(";",RIGHT(A1,LEN(A1)-SEARCH("H",A1)+1))),RIGHT(A1,LEN(A1)-SEARCH("H",A1)+1),LEFT(RIGHT(A1,LEN(A1)-SEARCH("H",A1)+1),SEARCH(";",RIGHT(A1,LEN(A1)-SEARCH("H",A1)+1))-1))

 

[27458]

Works great. Thank you!