Extracting characters from String

[jason_1]

I have a list fo ref numbers and need to extract the first 6 minus the H into the next cell, they are formatted as:

<TABLE style="WIDTH: 72pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=96 x:str><COLGROUP><COL style="WIDTH: 72pt; mso-width-source: userset; mso-width-alt: 3510" width=96><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; WIDTH: 72pt; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl24 height=17 width=96>H15383200101</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl24 height=17>H47832900101</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl24 height=17>H52032300101</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl24 height=17>H47822700101</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #c0c0c0; BORDER-LEFT: #c0c0c0; BACKGROUND-COLOR: transparent; HEIGHT: 12.75pt; BORDER-TOP: #c0c0c0; BORDER-RIGHT: #c0c0c0" class=xl24 height=17>H90000600101</TD></TR></TBODY></TABLE>

Is the a simple way to do this?

 

[agrawal.meet]

Put this in B1 assuming Codes are in A:A.

=MID(A1,2,6)

 

[doofusboy]

Assuming your data is in column A, put this formula in column B:

=RIGHT(LEFT(A1,6),5)

 

[Glob]

Assuming your data is in column A, put this formula in column B:

=RIGHT(LEFT(A1,6),5)

From reading the original post, since he wants the first 6 numbers after the "H", the above code is close, but only showing the first 5 numbers after the "H". A simple fix would be to change the "6" to a "7" and change the "5" to a "6" or

=RIGHT(LEFT(A1,7),6)

Also, the post 2 above this one works

=MID(A1,2,6)