Extracting characters from String

jason_1

New Member
Joined
Aug 11, 2009
Messages
10
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?
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

doofusboy

Well-known Member
Joined
Oct 14, 2003
Messages
1,325
Assuming your data is in column A, put this formula in column B:

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

Glob

New Member
Joined
Aug 5, 2010
Messages
2
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

Code:
=RIGHT(LEFT(A1,7),6)
Also, the post 2 above this one works

Code:
=MID(A1,2,6)
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,105,933
Messages
5,508,213
Members
408,670
Latest member
lhmwnrexcel

This Week's Hot Topics

Top