Formula to copy partial content of a cell? - Page 2
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 16 of 16

Thread: Formula to copy partial content of a cell?

  1. #11
    MrExcel MVP Russell Hauf's Avatar
    Join Date
    Feb 2002
    Location
    Portland, OR Area - USA
    Posts
    1,605
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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)

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

  2. #12
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.


  3. #13
    MrExcel MVP Russell Hauf's Avatar
    Join Date
    Feb 2002
    Location
    Portland, OR Area - USA
    Posts
    1,605
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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)

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

  4. #14
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    WOW!

    But why not


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

    Or

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

  5. #15
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Oops, double post, sorry.

  6. #16
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,752
    Post Thanks / Like
    Mentioned
    11 Post(s)
    Tagged
    1 Thread(s)

    Default

      
    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.


User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com