Remove characters from Cell then paste remaining characters to new cell

Podder1965

New Member
Joined
Feb 10, 2021
Messages
14
Office Version
  1. 2016
Platform
  1. Windows
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
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,456
Office Version
  1. 365
Platform
  1. Windows
Does it have to be vba?
Could you use this worksheet formula?

21 04 04.xlsm
AB
1XXX101
2XXX/101
3XXX0101
4XX1818
Number
Cell Formulas
RangeFormula
B1:B4B1=TEXT(IFERROR(RIGHT(A1,2)+0,RIGHT(A1,1)),"00")
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
6,007
Office Version
  1. 2016
Platform
  1. Windows
Hi,

Do you mean something like this?

Book3.xlsx
AB
1XXX101
2XXX/101
3XXX0101
4XXX/2020
Sheet890
Cell Formulas
RangeFormula
B1:B4B1=TEXT(MID(A1,MIN(FIND({0,1,2,3,4}+{0;5},A1&1/17)),2),"00")
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,456
Office Version
  1. 365
Platform
  1. Windows
Does it have to be vba?
If it does, then for the same layout as post #2, try

VBA Code:
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

New Member
Joined
Feb 10, 2021
Messages
14
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

21 04 04.xlsm
AB
1XXX101
2XXX/101
3XXX0101
4XX1818
Number
Cell Formulas
RangeFormula
B1:B4B1=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

New Member
Joined
Feb 10, 2021
Messages
14
Office Version
  1. 2016
Platform
  1. Windows
Hi,

Do you mean something like this?

Book3.xlsx
AB
1XXX101
2XXX/101
3XXX0101
4XXX/2020
Sheet890
Cell Formulas
RangeFormula
B1:B4B1=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

Well-known Member
Joined
Oct 27, 2005
Messages
20,556
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows

ADVERTISEMENT

Maybe this way
VBA Code:
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

New Member
Joined
Feb 10, 2021
Messages
14
Office Version
  1. 2016
Platform
  1. Windows
If it does, then for the same layout as post #2, try

VBA Code:
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,456
Office Version
  1. 365
Platform
  1. Windows
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:
1617513725701.png
) 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

Active Member
Joined
Nov 11, 2020
Messages
418
Office Version
  1. 2016
Platform
  1. Windows
Here is one quick created UDF.
Use this function as any other.
"=FormatCell("A1")"

VBA Code:
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
 
Last edited:

Forum statistics

Threads
1,136,865
Messages
5,678,219
Members
419,753
Latest member
Vj3006

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top