extract number from a alphanumerical string?

zabiullakhan

Active Member
Joined
Aug 30, 2010
Messages
310
i have string of the pattern as below and the right is the result i desire to have

<TABLE style="LINE-HEIGHT: 150%; WIDTH: 104pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=139><COLGROUP><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" width=75><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 13.5pt; mso-height-source: userset" height=18><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 56pt; HEIGHT: 13.5pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=18 width=75>abcd -2001</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" width=64 align=right>2001</TD></TR>Col A Col B
<TR style="HEIGHT: 14.25pt; mso-height-source: userset" height=19><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 14.25pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=19>abcd -2009</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right>2009</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>abc def ghi</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>abcd -1999</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" align=right>1999</TD></TR></TBODY></TABLE>

i want to extract the number if the cell contains and if the nuber is not present i want to display the string and i just wanna take the last 4 number of the string.

Please help me with a excel formula i dont wanna run a macro for this.
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

=IF(ISNUMBER(FIND("-",A1)),RIGHT(A1,4)+0,"")
 

Watch MrExcel Video

Forum statistics

Threads
1,109,487
Messages
5,529,162
Members
409,851
Latest member
Ingar
Top