Remove random series of #'s from the end of a cell

[davisj80]

Hello,

I'd like a way to remove numbers from the end of each cell. For example:

<table style="width: 511px; height: 189px;" border="0" cellpadding="0" cellspacing="0"><col style="mso-width-source:userset;mso-width-alt:10788;width:221pt" width="295"> <tbody><tr style="height:12.75pt" height="17"> <td class="xl63" style="height:12.75pt;width:221pt" width="295" height="17">eCleaner 2.02</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl63" style="height:12.75pt;border-top:none" height="17">BW MP3 OCX (for Visual Basic 6) 2.0.2</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl63" style="height:12.75pt;border-top:none" height="17">Hacha 3.5.0</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl63" style="height:12.75pt;border-top:none" height="17">Messenger Backup 3.4</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl63" style="height:12.75pt;border-top:none" height="17">Blu ray Player for Windows 1.0.0.0000</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl63" style="height:12.75pt;border-top:none" height="17">Pointy Thingz Cursor Set 7.1</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl63" style="height:12.75pt;border-top:none" height="17">17 handwriting fonts 15.1</td> </tr> </tbody></table>
I'd like to remove these version numbers from the software name.

NOTE: There are some cases, like in the second row, where the name includes a number that i don't want to remove.

 

[Domenic]

Assuming that A1 contains the text string, try...


=TRIM(LEFT(A1,FIND("^^",SUBSTITUTE(A1," ","^^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-1))

or

=TRIM(SUBSTITUTE(A1,TRIM(RIGHT(SUBSTITUTE(TRIM(A1)," ",REPT(" ",99)),99)),""))

 

[davisj80]

Thanks Domenic,

This is really, really close. Unfortunately there are other titles in my list without numbers at the end as well. Is there a way to check for the numbers first and then only make edits to them? You can see here that words are being left off of the cells without numbers:

<table width="944" border="0" cellpadding="0" cellspacing="0"><colgroup><col style="mso-width-source:userset;mso-width-alt:10788;width:221pt" width="295"> <col style="mso-width-source:userset;mso-width-alt:12946;width:266pt" width="354"> <col style="mso-width-source:userset;mso-width-alt:10788;width:221pt" width="295"> </colgroup><tbody><tr style="height:12.75pt" height="17"> <td class="xl66" style="height:12.75pt;width:221pt" width="295" height="17">Original</td> <td class="xl66" style="border-left:none;width:266pt" width="354">1st suggestion</td> <td class="xl66" style="border-left:none;width:221pt" width="295">2nd suggestion</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt;border-top:none" height="17">Bomberman Icons 1.0</td> <td class="xl65" style="border-top:none;border-left:none">Bomberman Icons</td> <td class="xl65" style="border-top:none;border-left:none">Bomberman Icons</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt;border-top:none" height="17">FreeJava 3.0</td> <td class="xl65" style="border-top:none;border-left:none">FreeJava</td> <td class="xl65" style="border-top:none;border-left:none">FreeJava</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt;border-top:none" height="17">Star Wars Font Collection 1.5.1Beta 7</td> <td class="xl65" style="border-top:none;border-left:none">Star Wars Font Collection 1.5.1Beta</td> <td class="xl65" style="border-top:none;border-left:none">Star Wars Font Collection 1.5.1Beta</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt;border-top:none" height="17">Marvosym TrueType Font 2000.0226</td> <td class="xl65" style="border-top:none;border-left:none">Marvosym TrueType Font</td> <td class="xl65" style="border-top:none;border-left:none">Marvosym TrueType Font</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt;border-top:none" height="17">Desktop Architect 2.01</td> <td class="xl65" style="border-top:none;border-left:none">Desktop Architect</td> <td class="xl65" style="border-top:none;border-left:none">Desktop Architect</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl67" style="height:12.75pt;border-top:none" height="17">CS Lite 1.4Firefox Add On</td> <td class="xl67" style="border-top:none;border-left:none">CS Lite 1.4Firefox Add</td> <td class="xl67" style="border-top:none;border-left:none">CS Lite 1.4Firefox Add</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl65" style="height:12.75pt;border-top:none" height="17">Alone in the Dark 4: The New Nightmare </td> <td class="xl65" style="border-top:none;border-left:none">Alone in the Dark 4: The New Nightmare</td> <td class="xl65" style="border-top:none;border-left:none">Alone in the Dark 4: The New</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl67" style="height:12.75pt;border-top:none" height="17">Dookudu Wallpaper</td> <td class="xl67" style="border-top:none;border-left:none">Dookudu</td> <td class="xl67" style="border-top:none;border-left:none">Dookudu</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl67" style="height:12.75pt;border-top:none" height="17">Medal of Honor: Allied Assault (Single Player)</td> <td class="xl67" style="border-top:none;border-left:none">Medal of Honor: Allied Assault (Single</td> <td class="xl67" style="border-top:none;border-left:none">Medal of Honor: Allied Assault (Single</td> </tr> <tr style="height:12.75pt" height="17"> <td class="xl67" style="height:12.75pt;border-top:none" height="17">AMIP 2.65Winamp Plugin</td> <td class="xl67" style="border-top:none;border-left:none">AMIP 2.65Winamp</td> <td class="xl67" style="border-top:none;border-left:none">AMIP 2.65Winamp</td> </tr> </tbody></table>

 

[Domenic]

This will first check if the last character is a number...

=IF(ISNUMBER(RIGHT(A1,1)+0),TRIM(LEFT(A1,FIND("^^",SUBSTITUTE(A1," ","^^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))-1)),A1)