Copying part of a cell to a new cell

Catalin.B · #7 01-27-2012, 09:41 AM

Quote:

Originally Posted by paulw793

Is there anyway to add onto the second formula to only copy digits and not characters?

To extract numbers from text, best is to use a macro, like this:
(can be set to extract decimal, and/or negative numbres, with the optional arguments:

Code:

Function ExtrageNumar(rCell As Range, _
     Optional CuZecimale As Boolean, Optional Negative As Boolean) As Double


Dim iCount As Integer, i As Integer, iLoop As Integer
Dim sText As String, strNeg As String, strDec As String
Dim lNum As String
Dim vVal, vVal2
    
    sText = rCell
    If CuZecimale = True And Negative = True Then
        strNeg = "-"
        strDec = ","
    ElseIf CuZecimale = True And Negative = False Then
        strNeg = vbNullString
        strDec = ","
    ElseIf CuZecimale = False And Negative = True Then
        strNeg = "-"
        strDec = vbNullString
    End If

    iLoop = Len(sText)
            For iCount = iLoop To 1 Step -1
                vVal = Mid(sText, iCount, 1)
                If IsNumeric(vVal) Or vVal = strNeg Or vVal = strDec Then
                    i = i + 1
                    lNum = Mid(sText, iCount, 1) & lNum
                        If IsNumeric(lNum) Then
                            If CDbl(lNum) < 0 Then Exit For
                        Else
                          lNum = Replace(lNum, Left(lNum, 1), "", , 1)
                        End If
                End If
                If i = 1 And lNum <> vbNullString Then lNum = CDbl(Mid(lNum, 1, 1))
            Next iCount

    ExtrageNumar = CDbl(lNum)

End Function

Then, in cell, use : =ExtrageNumar(E8;1;1) (second and third arguments are optional, and are used for negative and decimals)
note:
the code may need to be modified according to your regional settings:
on lines 13 and 16, strDec = "," is strDec = "." if this is your regional decimal delimiter

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
How can I delete the content of a cell in column if the cell value is more than 1000?	Learner7	Excel	2	06-27-2011 05:44 AM
How can I fill cell color starting from Cell D5 using Conditional formatting instead	Learner7	Excel	0	07-08-2010 05:50 AM
How can I make a cell color RED under the Today's date cell?	Learner7	Excel	2	07-08-2010 12:52 AM
Copying data from one cell to another automatically	mrphilk	Excel	4	06-10-2010 11:52 PM
Auto-populate an MS Word table cell with text from a diff cell?	dreamrthts	Word Tables	0	03-20-2009 01:49 PM