Remove Contents from Cell After Last Letter...

[mlarson]

Hi all! I'm hoping you can help me with this question. Thanks in advance to anybody that helps!

I would like to remove contents of cells after the last letter.
For example, if a cell reads "a1b25f34" I would want it to become "a1b25f"... the "34" being the contents removed after the last letter "f".
Another example, if a cell reads "x376bf26ya1" I would want the result "x376bf26ya" with the "1" removed.

I hope I am explaining this question well! Thanks!

 

[Fluff]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How about

+Fluff 1.xlsm
	A	B
1
2	a1b25f34	a1b25f
3	x376bf26ya1	x376bf26ya
Data

Cell Formulas
Range	Formula
B2:B3	B2	=LEFT(A2,MAX(IFERROR(FIND(CHAR(ROW($65:$90)),UPPER(A2)),0)))

 

[Jeffrey Mahoney]

I only know how to do this in a User Defined Function. This can be called like a regular Excel function.

Function RemLastNumbers(Str As String) As String
  Dim X As Long
  Dim A As String
  Dim Numbers As String
  
  Application.Volatile
  
  Numbers = "0123456789"
  RemLastNumbers = Str
  
  For X = Len(Str) To 1 Step -1
    A = Mid(Str, X, 1)
    If InStr(Numbers, A) = 0 Then
      RemLastNumbers = Left(Str, X)
      Exit For
    End If
  Next X
    
End Function

 

[Rick Rothstein]

Give this array-entered** formula a try...

=SUBSTITUTE(A1,RIGHT(A1,COUNT(-RIGHT(A1,ROW($1:$30)))),"")

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself

 

[Jeffrey Mahoney]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

How about

[RANGE=rs:3|cs:2|w:+Fluff 1.xlsm|cls:xl2bb-200|s:Data|tw:115][XR][XH][/XH][XH=w:60]A[/XH][XH=w:55]B[/XH][/XR][XR][XH]1[/XH][XD=ch:12.75][/XD][XD][/XD][/XR][XR][XH]2[/XH][XD=h:l|v:m|ch:12.75]a1b25f34[/XD][XD=h:l|v:m|cls:fx][FORMULA==LEFT(A2,MAX(IFERROR(FIND(CHAR(ROW($65:$90)),UPPER(A2)),0)))]a1b25f[/FORMULA][/XD][/XR][XR][XH]3[/XH][XD=h:l|v:m|ch:12.75]x376bf26ya1[/XD][XD=h:l|v:m|cls:fx][FORMULA==LEFT(A3,MAX(IFERROR(FIND(CHAR(ROW($65:$90)),UPPER(A3)),0)))]x376bf26ya[/FORMULA][/XD][/XR][/RANGE][RANGE=cls:xl2bb-extra-200|t:cf][XR][XD]B2:B3[/XD][XD=fw:b]B2[/XD][XD]=LEFT(A2,MAX(IFERROR(FIND(CHAR(ROW($65:$90)),UPPER(A2)),0)))[/XD][/XR][/RANGE]

It looks like a few formatting strings got added by xl2bb

 

[Fluff]

I accidentality put the mini-sheet code in code tags, but have now corrected it.

 

[Jeffrey Mahoney]

Give this array-entered** formula a try...

=SUBSTITUTE(A1,RIGHT(A1,COUNT(,-RIGHT(A1,ROW($1:$30)))-1),"")

**Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself

Ok, why does this work??? brain hurting!

 

[mlarson]

Thank you very much, Fluff, this worked! I'm curious why the 65 and 90 are in the formula?

 

[Fluff]

65 is the ascii code for A & 90 is Z so the formula finds the last letter in the string.

 

[mlarson]

Good to know, thanks!

And thank you to everybody else that responded!