Formula to extract value from text

[howard]

I have the following text and would like a formula to extract the value for eg 60, 72 etc

Depreciation straight line 60 months
Depreciation straight line 72 months

 

[mathchick]

if the surrounding text is always the same and in cell A1:
=mid(A1,28,len(A1)-34)

 

[Akashwani]

Hi Howard.

Take a look here....

http://www.mrexcel.com/forum/excel-questions/443983-extract-only-numbers-text-string.html

I hope that helps.

Ak

 

[ukmikeb]

Hi

Look at post #4 here - http://www.mrexcel.com/forum/excel-questions/463005-extract-number-alphanumeric-string.html

and change "G/L" to "month" in the FIND part.

hth

 

[GGK]

What about a formula for the reverse function?

I have a SUBSTITUTE word formula that replaces words in a URL... but the conversion cell shows the formula. How would i convert that into just text in the next cell over so i can copy it right for the cell and paste in a browser (Without using a HYPERLINK formula)?

Thanks!

Hi Howard.

Take a look here....

http://www.mrexcel.com/forum/excel-questions/443983-extract-only-numbers-text-string.html

I hope that helps.

Ak

 

[vds1]

VBA Code:

select the range and keep the column next to it empty . Run the macro

Sub Remove_Alphabets_SpecialChar_Test()
     
    Dim RegX As Object
    Dim Rng As Range
     
    Set RegX = CreateObject("vbscript.regexp")
     
    With RegX
        .Global = True
        .Pattern = "[^-0-9]"
    End With
     
    For Each Rng In selection
        Rng.Offset(, 1) = RegX.Replace(Rng, "")
    Next Rng
     
End Sub

 

[GGK]

Can it be done with a formula?

Cell B2 shows the URL... but in the formula bar shows the formula... how can i get the URL to show up in the Formula Bar?

VBA Code:

select the range and keep the column next to it empty . Run the macro

Sub Remove_Alphabets_SpecialChar_Test()
     
    Dim RegX As Object
    Dim Rng As Range
     
    Set RegX = CreateObject("vbscript.regexp")
     
    With RegX
        .Global = True
        .Pattern = "[^-0-9]"
    End With
     
    For Each Rng In selection
        Rng.Offset(, 1) = RegX.Replace(Rng, "")
    Next Rng
     
End Sub

 

[GGK]

sorry. i meant... is there a way to set a cell as a Paste Special Value and have the conversion take place there?

 

[GGK]

No worries.. Figured it out.