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

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,561
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,127,261
Messages
5,623,692
Members
415,983
Latest member
MusicMan

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