Trim String after last number

  • Thread starter Thread starter Bobby
  • Start date Start date
B

Bobby

I have a string that is both text and numeric (ie:A123BC), the last
charectors afte the number does not always equal 2 so a right of left
formula will not cut it. Besides the Right and left function how can I
drop a formula that will always trim after the last numeric value, so
that A123BC will return A123.

Thanks fo rthe help
 
Bobby
Could you provide some more description of the strings. Do they fall within some minimum and maximum length? Do the letter on the end fall within some minimum and maximum length

Depending on the complextiy of the data set, this can probably be done with some combination of IF, LEN, RIGHT, MID, LEFT, FIND, and/or ISNUM functions

Regards
Mark Graesse
(e-mail address removed)

----- Bobby wrote: ----

I have a string that is both text and numeric (ie:A123BC), the las
charectors afte the number does not always equal 2 so a right of lef
formula will not cut it. Besides the Right and left function how can
drop a formula that will always trim after the last numeric value, s
that A123BC will return A123

Thanks fo rthe hel
 
Bobby,

Paste this UDF into a regular module in the VBE:

Function TrimAlpha(Indata) As String
TrimAlpha = Indata
Do
i = Len(TrimAlpha)
If Mid(TrimAlpha, i, 1) >= 0 And Mid(TrimAlpha, i, 1) <= 9 Then
Exit Do
Else
TrimAlpha = Left(TrimAlpha, i - 1)
If i = 0 Then Exit Do
End If
Loop
End Function

Then call it in a sheet:
=TrimAlpha(A2)
 
Hi Mark and Bobby!

Agreed on need for more data. Another alternative might be to add
columns to the right and use Data > Text to Columns with fixed width
to separate out all characters. Then use a formula like:

=A1&B1&IF(ISNUMBER(C1),C1,"")&IF(ISNUMBER(D1),D1,"")&IF(ISNUMBER(E1),E
1,"")&IF(ISNUMBER(F1),F1,"")&IF(ISNUMBER(G1),G1,"")

But more examples will surely lead to more elegant and / or certain
solutions.

--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Bobby

One way assuming string in D7:

=LEFT(D7,MAX((ISNUMBER(VALUE(MID(D7,ROW(
INDIRECT("1:"&LEN(D7))),1))))*ROW(INDIRECT("1:"&LEN(D7)))))

The formula is an array formula and must be entered with
<Shift><Ctrl><Enter>, also if edited later. If done correctly,
Excel will display the formula in the formula bar enclosed in
curly brackets { }. Don't enter these brackets yourself.
 
This array formula should work

=LEFT(A1,MATCH(FALSE,ISERROR(--MID(A1,ROW(INDIRECT("1:100")),1))))

entered with ctrl + shift & enter

it will fail if there are no numbers or only one number

if there is a possibility there are single numbers like in ABC1BC you can
use

=IF(ISNA(MATCH(FALSE,ISERROR(--MID(A2,ROW(INDIRECT("1:100")),1)))),LEFT(A2,M
ATCH(FALSE,ISERROR(--MID(A2,ROW(INDIRECT("1:100")),1)),0)),LEFT(A2,MATCH(FAL
SE,ISERROR(--MID(A2,ROW(INDIRECT("1:100")),1)))))

entered with ctrl + shift & enter

copy down as long as needed
 
If one is comfortable with Regular Expressions, another idea might be
something like this. It may be a hair slower though, but it has no loops.

Option Explicit
Public RegExp As RegExp

Function RemoveEndingLetters(s As String) As String
'//Ref: Microsoft VBScript Regular Expressions 5.5
If RegExp Is Nothing Then Set RegExp = New RegExp
RegExp.Pattern = "\D+$"
RemoveEndingLetters = RegExp.Replace(s, vbNullString)
End Function
 
Building on Dana's suggestion of a regular expression, if you install
the functions at http://www.tmehta.com/regexp/add_code.htm
you could use something like =RegExpSubstitute(A1,"\D+$","")
though \D+$ does imply any trailing non-digits, not just letters.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Back
Top