Extract portion of text.

[Xirom431]

Hi,

I have these strings of numbers & text.

JA01_XV_0990
JA01_ZSL_0990L

I want to break them into 3 columns

JA01 ZSL 0990L

For the 0990L or 0990, I have this formula and it works for the 0990L

=IF(E9291="","",RIGHT(E9291,FIND("_",E9291))) but will not work for 0990.
I have this =IF(E3="","",RIGHT(E3,FIND("_",E3)-1)) for 0990. Is there a way I can do for both without usin 2 separate formulas?

Thanks. Using Excel 2010.

 

[VoG]

Why not Data > Text to Columns, Delimited with _ as the delimiter.

 

[Trevor G]

Use text to columns. Just tried this on your sample it works fine, under the Data Tab, Text to Columns then use the Other Delimiter and use _

Shown below is sample once used.

Sheet7

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"><COL style="WIDTH: 64px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>A</TD><TD>B</TD><TD>C</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>JA01</TD><TD>XV</TD><TD style="TEXT-ALIGN: right">990</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD>JA01</TD><TD>ZSL</TD><TD>0990L</TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4