Formula to copy partial content of a cell?

[Guest]

Question:
I am wondering is there an excel formula to copy one partial content of a cell to another cell?
eg. Copy 90 from Weight-90 in a cell to another cell
Thanks in advance

 

[Russell Hauf]

How about a User-Defined Function?

If you put this into a module in your workbook, you can use the function like this:

=ExtractNum(A1)

Public Function ExtractNum(rng As Range) As Double

    Dim strTemp As String
    
    strTemp = rng.Text
    Do While Not IsNumeric(Left(strTemp, 1))
        strTemp = Right(strTemp, Len(strTemp) - 1)
    Loop
    
    On Error Resume Next
    ExtractNum = Val(strTemp)
    
End Function

Hope this helps,

Russell

 

[Dave Hawley]

WOW! this is getting huge!

Why not just:


=SUBSTITUTE(D14,"kg","")*1

Or

=SUBSTITUTE(SUBSTITUTE(D2,"kg",""),"Weight=","")*1

If it actualy contains the text "Weight=" as well.

 

[Russell Hauf]

On 2002-03-12 09:45, Russell Hauf wrote:
How about a User-Defined Function?

If you put this into a module in your workbook, you can use the function like this:

=ExtractNum(A1)

Public Function ExtractNum(rng As Range) As Double

    Dim strTemp As String
    
    strTemp = rng.Text
    Do While Not IsNumeric(Left(strTemp, 1))
        strTemp = Right(strTemp, Len(strTemp) - 1)
    Loop
    
    On Error Resume Next
    ExtractNum = Val(strTemp)
    
End Function

Hope this helps,

Russell

Note that if your cell has 2 numbers in it that are separated by text (ex: Weight=90kg20mg), it will only return the first number.

-rh

 

[Dave Hawley]

WOW!

But why not


=SUBSTITUTE(D14,"kg","")*1

Or

=SUBSTITUTE(SUBSTITUTE(D2,"kg",""),"Weight=","")*1

 

[Dave Hawley]

Oops, double post, sorry.

 

[Aladin Akyurek]

On 2002-03-12 08:19, Anonymous wrote:
Question:
I am wondering is there an excel formula to copy one partial content of a cell to another cell?
eg. Copy 90 from Weight-90 in a cell to another cell
Thanks in advance

Care to post a few examples of text values from which you want to extract the num part?

It's important to know whether there is any kind of regularity in the entries, which could be exploited by a formula.