Search for Certain Part of String

[Onjslnjps]

Hi All,

Currently in an excel spreadsheet I have values within cells such as:

"WIE - Infrastructure Equity Fund"

Is there anyway to save ONLY the last part of the above value in an array or something?

i.e only save "Infrastructure Equity Fund"

Thank you for your help!!

Kind Regards

 

[stwpkcmo]

Not sure if this is what you are looking for, but assuming you just want to populate everything to the right of the "-" and this is a consistent format the following formula should work for you:<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
<TABLE style="WIDTH: 545pt; BORDER-COLLAPSE: collapse; mso-yfti-tbllook: 1184; mso-padding-alt: 0in 0in 0in 0in" class=MsoNormalTable border=0 cellSpacing=0 cellPadding=0 width=727><TBODY><TR style="HEIGHT: 15pt; mso-yfti-irow: 0; mso-yfti-firstrow: yes; mso-yfti-lastrow: yes"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; WIDTH: 545pt; PADDING-RIGHT: 0in; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" width=727>=TRIM(REPLACE(A1,1,SEARCH("^^",SUBSTITUTE(A1,"-","^^",LEN(A1)-LEN(SUBSTITUTE(A1,"-","")))),""))<o></o>
</TD></TR></TBODY></TABLE>
<o></o>
Obviously all of the A1's are replaced by the cell you are applying the formula to . . . It should return: <o></o>
<TABLE style="WIDTH: 325pt; BORDER-COLLAPSE: collapse; mso-yfti-tbllook: 1184; mso-padding-alt: 0in 0in 0in 0in" class=MsoNormalTable border=0 cellSpacing=0 cellPadding=0 width=433><TBODY><TR style="HEIGHT: 15pt; mso-yfti-irow: 0; mso-yfti-firstrow: yes; mso-yfti-lastrow: yes"><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; PADDING-BOTTOM: 0in; BACKGROUND-COLOR: transparent; PADDING-LEFT: 0in; WIDTH: 325pt; PADDING-RIGHT: 0in; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; PADDING-TOP: 0in" width=433>Infrastructure Equity Fund<o></o>
<o></o>
Let me know if that helps at all.<o></o>
</TD></TR></TBODY></TABLE>

 

[Robert Mika]

Sheet1

<table style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; " border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight:bold; width:30px; "><col style="width:221px;"><col style="width:207px;"></colgroup><tbody><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td>*</td><td>A</td><td>B</td></tr><tr style="height:18px ;"><td style="font-size:8pt; background-color:#cacaca; text-align:center; ">1</td><td>WIE - Infrastructure Equity Fund</td><td>Infrastructure Equity Fund</td></tr></tbody></table>
<table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tbody><tr><td>Spreadsheet Formulas</td></tr><tr><td><table style="font-family:Arial; font-size:9pt;" border="1" cellpadding="2" cellspacing="0"><tbody><tr style="background-color:#cacaca; font-size:10pt;"><td>Cell</td><td>Formula</td></tr><tr><td>B1</td><td>=TRIM(MID(A1,FIND("-",A1)+1,LEN(A1)))</td></tr></tbody></table></td></tr></tbody></table>
Assuming that the quotes " are not a part of the string.

Excel tables to the web >> Excel Jeanie HTML 4

 

[Peter_SSs]

Is there anyway to save ONLY the last part of the above value in an array or something?

"array or something" is a bit vague and one line of sample data isn't much to go on, but you could try this

1. Select the column by clicking its heading label

2. Do a Find/Replace and in the 'Find what' box put (without the "" as I've used those to ensure you see the spaces) "* - " and leave the 'Replace with' box empty.

3. Make sure the 'Options' are showing and that 'Match entire cell contents' is not checked.

4. Replace All