Copy only numbers

oreo haven

Board Regular
Joined
May 15, 2008
Messages
61
I have a Value Stream Map where I need to have a formula look into a cell and only copy the number in that cell....ie. cell A2 has the following : Cycle time = 33 minutes.

I have several places that are going to refer to that 33 minutes, however, I just want the cells to display 33. Does anyone know how I can write a formula that will do that? I am so new to excel that the only thing I have tried to do is cellX=A2 Of course I know that this will not work, but I don't know what will.
Any and all help is appreciated.
Jon
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,744
Office Version
  1. 365
Platform
  1. Windows
Here is a User Defined Function that will take a string and return just the numbers from it (if there are no numbers, it will return 0).
Code:
Function GetNumber(MyEntry As String)
    
    Dim i As Byte
    
    If Len(MyEntry) > 0 Then
        For i = 1 To Len(MyEntry)
            If IsNumeric(Mid(MyEntry, i, 1)) Then
                GetNumber = GetNumber & Mid(MyEntry, i, 1)
            End If
        Next i
    End If
    
End Function
If you turn on the Macro Recorder, then press Stop, then go to Tools | Macro, then highlight "Macro1" and click Edit, it will take you into the VB Editor. Simply paste the code above what is in there and Save and Exit.

Then you can use this function like any other native Excel function. So if your entry is in cell A2, then this formula would return the numeric part of that value:

=GetNumber(A2)
 

litestream

Active Member
Joined
Jul 24, 2006
Messages
323
Thanks for that. When I searched I found plenty of solutions for removing the numbers but not the letters.
 

Watch MrExcel Video

Forum statistics

Threads
1,128,121
Messages
5,628,805
Members
416,342
Latest member
BlueDevil12

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