# Extract Text and Number

#### 27458

##### Board Regular
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

### Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
try this

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

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

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))

Works great. Thank you!

Replies
19
Views
567
Replies
2
Views
283
Replies
4
Views
241
Replies
18
Views
600
Replies
1
Views
458

1,203,464
Messages
6,055,577
Members
444,799
Latest member
CraigCrowhurst

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back