delating extra letters and numbers

wolf735

New Member
Joined
Oct 4, 2009
Messages
49
Office Version
  1. 365
Platform
  1. Windows
Hello, i'm having trouble deleting extra letters and or numbers form my data sets. how delete extra data without "corrupting" data. this is an example of data im using.

<table style="border-collapse: collapse; width: 48pt;" width="64" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" width="64"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl70" style="height: 15pt; width: 48pt;" width="64" height="20">Arabic I</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl70" style="height: 15pt;" height="20">Arabic Ii</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl70" style="height: 15pt;" height="20">Chinese Ii</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl70" style="height: 15pt;" height="20">Crime Scene Investigation</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl70" style="height: 15pt;" height="20">Food Preparation</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl70" style="height: 15pt;" height="20">French I</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl70" style="height: 15pt;" height="20">French Ii</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl70" style="height: 15pt;" height="20">German I</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl70" style="height: 15pt;" height="20">Hindi I</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl70" style="height: 15pt;" height="20">Hindi Ii</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl70" style="height: 15pt;" height="20">Indonesian I</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl70" style="height: 15pt;" height="20">Indonesian Ii</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl70" style="height: 15pt;" height="20">Italian I</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl70" style="height: 15pt;" height="20">Italian Ii</td><td valign="top">
</td><td valign="top">
</td><td valign="top">
</td> </tr> </tbody></table>my problem is i have no clue as to how to get the extra i's at end out.
<table style="border-collapse: collapse; width: 48pt;" width="64" border="0" cellpadding="0" cellspacing="0"><tbody></tbody></table>
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

xld

Banned
Joined
Feb 8, 2003
Messages
5,378
Try

=IF(RIGHT(A2,2)="Ii",LEFT(A2,LEN(A2)-1),A2)
 

gsbelbin

Active Member
Joined
Aug 29, 2008
Messages
336
You could try

=IF(RIGHT(A1,1)="i",LEFT(A1,LEN(A1)-1),A1)

Note that this will not allow for cells that contain something like Hindi. It just looks for an i at the end
 

wolf735

New Member
Joined
Oct 4, 2009
Messages
49
Office Version
  1. 365
Platform
  1. Windows
i tryed both ways got ether (0) or (#value!)
 

wolf735

New Member
Joined
Oct 4, 2009
Messages
49
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

it worked the computer was having user errors sorry guys and thanks
 

bosco_yip

Well-known Member
Joined
Dec 2, 2002
Messages
1,940
Office Version
  1. 2019
Platform
  1. Windows
Or, this :

=SUBSTITUTE(A1,"Ii","I")

Regards
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,699
Messages
5,833,210
Members
430,197
Latest member
edeibold

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top