Equation to remove last character if letter and first leading 0

[NormChart55]

Hello,

I am looking for a way to remove letters if they appear at the end of an item as well as removing a 0 only if it appears between 000 and 099 after the characters "RV". I have an example list below and hoping there is an easy way to remove these from a mass list with a formula. Thanks for any information and help.

Examples
Store numbers - always starts with RV and could go up to RV9999a (or b,c,d,e)

RV002A - should be RV02
RV050G - should be RV50
RV027H - should be RV27
RV1000 - should be RV1000
RV429A - should be RV429

 

[Joe4]

Will there ever only be one single letter at the end, or could there be more than one?

 

[Joe4]

If there will never be more than one letter at the end of the entry, then try this formula (for an entry in cell A1):

=LEFT(A1,2) & IFERROR(MID(A1,3,LEN(A1)-2)+0,MID(A1,3,LEN(A1)-3)+0)

 

[lrobbo314]

Another way using VBA.

NORM55
	B	C
1	RV002A	RV02
2	RV050G	RV50
3	RV027H	RV27
4	RV1000	RV1000
5	RV429A	RV429
Sheet5

Cell Formulas
Range	Formula
C1:C5	C1	=NORM55(B1)

Function NORM55(s As String)
With CreateObject("VBScript.RegExp")
    .Pattern = "(RV)(\d+)"
    Set matches = .Execute(s)(0).submatches
    NORM55 = matches(0) & _
    Format(Int(matches(1)), "00")
End With
End Function

 

[awoohaw]

how about:

mr excel questions 20.xlsm
	A	B
1	RV002A	RV02
2	RV050G	RV50
3	RV027H	RV27
4	RV1000	RV1000
5	RV429A	RV429
Sheet5

Cell Formulas
Range	Formula
B1:B5	B1	=SUBSTITUTE(IF(ISNUMBER(VALUE(RIGHT(A1,1))),A1,LEFT(A1,LEN(A1)-1)),"RV0","RV",1)

 

[NormChart55]

If there will never be more than one letter at the end of the entry, then try this formula (for an entry in cell A1):

=LEFT(A1,2) & IFERROR(MID(A1,3,LEN(A1)-2)+0,MID(A1,3,LEN(A1)-3)+0)

Thanks for the information. There is only ever 1 letter. I applied this one, but I am getting the stores that are between 1-10 do not have a 0.

Example: WZ002A -> goes to WZ2 but should be WZ02. All others are coming out as expected

 

[Fluff]

Another option

=REPLACE(LEFT(A2,LEN(A2)-ISERR(RIGHT(A2)+0)),3,MID(A2,3,1)="0","")

 

[NormChart55]

how about:

mr excel questions 20.xlsm
	A	B
1	RV002A	RV02
2	RV050G	RV50
3	RV027H	RV27
4	RV1000	RV1000
5	RV429A	RV429
Sheet5

Cell Formulas
Range	Formula
B1:B5	B1	=SUBSTITUTE(IF(ISNUMBER(VALUE(RIGHT(A1,1))),A1,LEFT(A1,LEN(A1)-1)),"RV0","RV",1)

Thanks. This one removes the letter but still leaves the leading 0s for me

 

[NormChart55]

Another way using VBA.

NORM55
	B	C
1	RV002A	RV02
2	RV050G	RV50
3	RV027H	RV27
4	RV1000	RV1000
5	RV429A	RV429
Sheet5

Cell Formulas
Range	Formula
C1:C5	C1	=NORM55(B1)

Function NORM55(s As String)
With CreateObject("VBScript.RegExp")
    .Pattern = "(RV)(\d+)"
    Set matches = .Execute(s)(0).submatches
    NORM55 = matches(0) & _
    Format(Int(matches(1)), "00")
End With
End Function

Thanks for the information. I tried this one, I added a module with the attached VBA and applied that formula but its giving me a #VALUE?

 

[NormChart55]

Another option

=REPLACE(LEFT(A2,LEN(A2)-ISERR(RIGHT(A2)+0)),3,MID(A2,3,1)="0","")

Thank you for the information. This one did exactly as I expected/wanted. Very much appreciated, you guys amaze me. thanks all for the help!