Remove characters from Cell then paste remaining characters to new cell

[Podder1965]

Hi,
I am trying to remove characters from a cells then return the remaining values to a new cell, however, there are certain criteria's I require.
The cells are in column B and will consist of the following:
XXX1
XXX/1
XXX01
Note the last digit may be 2 or 3 etc upto 20.
What I require is to remove all characters before the number but need to have a zero before a single digit number e.g. XXX1 to become 01 or XXX/1 to become 01, but XXX/20 to be 20
I also would like the value "01" to be text when completed.
Is it possible to achieve this in excel vba?

Thanks

 

[Peter_SSs]

Does it have to be vba?
Could you use this worksheet formula?

21 04 04.xlsm
	A	B
1	XXX1	01
2	XXX/1	01
3	XXX01	01
4	XX18	18
Number

Cell Formulas
Range	Formula
B1:B4	B1	=TEXT(IFERROR(RIGHT(A1,2)+0,RIGHT(A1,1)),"00")

 

[jtakw]

Hi,

Do you mean something like this?

Book3.xlsx
	A	B
1	XXX1	01
2	XXX/1	01
3	XXX01	01
4	XXX/20	20
Sheet890

Cell Formulas
Range	Formula
B1:B4	B1	=TEXT(MID(A1,MIN(FIND({0,1,2,3,4}+{0;5},A1&1/17)),2),"00")

 

[Peter_SSs]

Does it have to be vba?

If it does, then for the same layout as post #2, try

Sub RightNumber()
  With Range("A1", Range("A" & Rows.Count).End(xlUp))
    .Offset(, 1).NumberFormat = "@"
    .Offset(, 1).Value = Evaluate(Replace("text(IFERROR(RIGHT(#,2)+0,RIGHT(#,1)),""00"")", "#", .Address))
  End With
End Sub

 

[Podder1965]

Does it have to be vba?
Could you use this worksheet formula?

21 04 04.xlsm
	A	B
1	XXX1	01
2	XXX/1	01
3	XXX01	01
4	XX18	18
Number

Cell Formulas
Range	Formula
B1:B4	B1	=TEXT(IFERROR(RIGHT(A1,2)+0,RIGHT(A1,1)),"00")

Peter thanks your solution does work, if possible would prefer it in vba.
Thanks anyway much appreciated

 

[Podder1965]

Hi,

Do you mean something like this?

Book3.xlsx
	A	B
1	XXX1	01
2	XXX/1	01
3	XXX01	01
4	XXX/20	20
Sheet890

Cell Formulas
Range	Formula
B1:B4	B1	=TEXT(MID(A1,MIN(FIND({0,1,2,3,4}+{0;5},A1&1/17)),2),"00")

jtakw,
Thanks your solution also works, thanks for your input.
As I said to peter, would prefer in vba, if possible
Thanks

 

[Michael M]

Maybe this way

Function getnumerals(C As Variant)
"original code by Yujin
For i = 1 To Len(C)
ntt = Mid(C, i, 1)
If IsNumeric(ntt) Then
 ott = ott & ntt
End If
Next i
If Len(ott) = 1 Then ott = "0" & ott
getnumerals = ott
End Function

 

[Podder1965]

If it does, then for the same layout as post #2, try

Sub RightNumber()
  With Range("A1", Range("A" & Rows.Count).End(xlUp))
    .Offset(, 1).NumberFormat = "@"
    .Offset(, 1).Value = Evaluate(Replace("text(IFERROR(RIGHT(#,2)+0,RIGHT(#,1)),""00"")", "#", .Address))
  End With
End Sub

Peter,
I get an error with the Replace part stating wrong number of arguments or invalid property assign, any idea why?

 

[Peter_SSs]

Peter,
I get an error with the Replace part stating wrong number of arguments or invalid property assign, any idea why?

What is the exact error message?
Did you copy/paste the code from the forum (you can use the icon at the top right of the forum code pane:

) or did you re-type or edit the code yourself?
If not a direct copy/paste then please post the actual code that you tried.

 

[EXCEL MAX]

Here is one quick created UDF.
Use this function as any other.
"=FormatCell("A1")"

Function FormatCell(ByVal vR As Range)

    Dim vN As Integer, vN2 As Integer
    Dim vF As String, vS As String

    vS = vR
    For vN = 1 To Len(vR)
        If IsNumeric(Mid(vS, vN, 1)) Then
            For vN2 = 1 To Len(vS) - (vN - 1)
                vF = vF & "0"
            Next
            FormatCell = Format(Right(vS, Len(vS) - (vN - 1)), vF)
            If Not Left(FormatCell, 1) = "0" And Not Len(FormatCell) > 1 Then _
                FormatCell = Format(Right(vS, Len(vS) - (vN - 1)), "0" & vF)
            Exit Function
        End If
    Next
    If IsEmpty(FormatCell) Then FormatCell = ""

End Function