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]

Glad we could help & thanks for the feedback.

 

[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

Actually, in looking at this further, my formula can be shortened quite a bit to this still array-entered** formula...

=LEFT(A1,LEN(A1)-COUNT(-RIGHT(A1,ROW($1:$30))))

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

 

[Rick Rothstein]

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

If someone wants to do this with a UDF function, here is how I would probably write it (no loops)...

Function RemoveLastNum(S As String) As String
  Dim RevS As String
  RevS = StrReverse(S)
  RemoveLastNum = StrReverse(Mid(RevS, InStr(RevS, Val(RevS)) + Len(CStr(Val(RevS)))))
End Function

@jeffrey... I revised my formula in Message #12 so I am wondering if you still want an explanation for my first formula (which I have revised twice now) or my latest formula?

 

[Jeffrey Mahoney]

@Rick Rothstein

I just don't get the last portion of the formula in the "Right" function. The row function returns an array of row numbers. Still shaking my head
=LEFT(A1,LEN(A1)-COUNT(-RIGHT(A1,ROW($1:$30))))

 

[Rick Rothstein]

The RIGHT function using ROW($1:$30) is examining the last character, then the last two characters, then the last three character and so on up to a possible total of 30 characters. The minus sign then attempts to make each a number... it will successfully do so until the rightmost N characters include a non-digit after which it will evaluate as a #VALUE! error. So the number of substrings it successfully converts to numbers is the same number as there are digits at the end of the text... and COUNT counts them. The formula then uses that number to adjust the characters to return from the original text.

 

[Jeffrey Mahoney]

I see the logic now. Thank you. Surprising though is that it doesn't return #VALUE! once the number of characters in the main string are less.

 

[Rick Rothstein]

I see the logic now. Thank you. Surprising though is that it doesn't return #VALUE! once the number of characters in the main string are less.

The RIGHT function returns the entire text string if you ask for more characters than it contains. For example...

=RIGHT("ABC",1000)

returns "ABC" as its result. So -RIGHT just keeps generating #VALUE! errors when it runs out of characters up to the 30 characters it was asked to process... since COUNT ignores errors, it only returns the count of the numbers it sees no matter how many #VALUE! errors were calculated.

 

[mlarson]

Hi there, I'm encountering a problem with the following formula. It works most of the time but in some instances it doesn't (as explained below)...
LEFT(H17,MAX(IFERROR(FIND(CHAR(ROW($65:$90)),UPPER(H17)),0)))

For example, "1a2a12" is in cell H17, and it returns "1a" instead of the desired "1a2a".
Another example, "1b3b23" returns "1b" instead of the desired "1b3b"
So it seems the problem has something to do with the same letter appearing multiple times in the cell ("a" for the first example, "b" for the second example).

The problem doesn't occur for other entries.
For example, "1a2b13" results in "1a2b" (correct).
Another example, "1b3d25" results in "1b3d" (correct).

Any ideas on how I can rectify this issue? Thanks in advance for your help!

 

[Fluff]

I didn't take into account repeated letters, use Rick's formula from post#12

 

[mlarson]

I didn't take into account repeated letters, use Rick's formula from post#12

Thanks, that worked! Looks like it works even if not entered as an array-entered formula.