Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have a column of data that has verbage like so:

    Individual copay is 30 etc etc.

    I would like to create another column that contains just the 30.

    Is there a function that I could add to my Persnal.xls that does this? Thanks for your help...

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

  3. #3
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    [quote name='LindaR' post='800356' date='28-Oct-2009 17:52']I have a column of data that has verbage like so:

    Individual copay is 30 etc etc.

    I would like to create another column that contains just the 30.

    Is there a function that I could add to my Persnal.xls that does this? Thanks for your help...[/quote]

    Or use this found at
    http://www.automateexcel.com/2008/11/03/vb...er-from-string/

    Code:
    Function Extract_Number_from_Text(Phrase As String) As Double
    Dim Length_of_String As Integer
    Dim Current_Pos As Integer
    Dim Temp As String
    Length_of_String = Len(Phrase)
    Temp = ""
    For Current_Pos = 1 To Length_of_String
    If (Mid(Phrase, Current_Pos, 1) = "-") Then
      Temp = Temp & Mid(Phrase, Current_Pos, 1)
    End If
    If (Mid(Phrase, Current_Pos, 1) = ".") Then
     Temp = Temp & Mid(Phrase, Current_Pos, 1)
    End If
    If (IsNumeric(Mid(Phrase, Current_Pos, 1))) = True Then
    	Temp = Temp & Mid(Phrase, Current_Pos, 1)
     End If
    Next Current_Pos
    If Len(Temp) = 0 Then
    	Extract_Number_from_Text = 0
    Else
    	Extract_Number_from_Text = CDbl(Temp)
    End If
    End Function
    Andrew

  4. #4
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='800361' date='28-Oct-2009 18:12']See extracting numbers from within text (XL97 or 2)[/quote]


    How would I use this function?

    Function ExtractNumber(vValue)
    Dim x As Integer
    For x = 1 To Len(vValue)
    If Val(Mid(vValue, x)) <> 0 Then
    ExtractNumber = Val(Mid(vValue, x))
    Exit Function
    End If
    Next
    End Function


    I will be putting the code in my Personal.xls. =Personal.xls!ExtractNumber(A2). Would this be the syntax?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='LindaR' post='800366' date='28-Oct-2009 19:27']I will be putting the code in my Personal.xls. =Personal.xls!ExtractNumber(A2). Would this be the syntax?[/quote]
    Yes, that is correct.

  6. #6
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='AndrewKKWalker' post='800364' date='28-Oct-2009 19:16']Or use this found at
    http://www.automateexcel.com/2008/11/03/vb...er-from-string/

    Code:
    Function Extract_Number_from_Text(Phrase As String) As Double
    Dim Length_of_String As Integer
    Dim Current_Pos As Integer
    Dim Temp As String
    Length_of_String = Len(Phrase)
    Temp = ""
    For Current_Pos = 1 To Length_of_String
    If (Mid(Phrase, Current_Pos, 1) = "-") Then
      Temp = Temp & Mid(Phrase, Current_Pos, 1)
    End If
    If (Mid(Phrase, Current_Pos, 1) = ".") Then
     Temp = Temp & Mid(Phrase, Current_Pos, 1)
    End If
    If (IsNumeric(Mid(Phrase, Current_Pos, 1))) = True Then
    	Temp = Temp & Mid(Phrase, Current_Pos, 1)
     End If
    Next Current_Pos
    If Len(Temp) = 0 Then
    	Extract_Number_from_Text = 0
    Else
    	Extract_Number_from_Text = CDbl(Temp)
    End If
    End Function
    [/quote]

    I used the above function and if the sentence has more than one number, it brings both and runs them together. Where in the code can you put a space between numbers if there is more than one number? Thanks for your help.l

  7. #7
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    [quote name='LindaR' post='800371' date='28-Oct-2009 19:06']I used the above function and if the sentence has more than one number, it brings both and runs them together. Where in the code can you put a space between numbers if there is more than one number? Thanks for your help.l[/quote]

    It isn't my function, it was just free on the net.
    There is no place in this function to obviously do that.
    It just extracts the numbers from a string.

    It doesn't look for multiple occurrences of a numbers and separate them.

    You said you wanted to extract the number into a cell.
    If you extract more than one number then the return value
    would be text not a number.

    What do you really want to get back out of the string.
    Andrew

  8. #8
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    If you want a function that extracts all Numbers out as a string separated by some character,
    you could use this one.

    You have to supply the Cell with the Phrase in and a suitable Separator to use when a number is found.
    If only one it will be ignored

    I am sure there are more elegant solutions, but it works for me.

    It would be entered in the form

    =ExtractNumbers(A1," / ") if for example you wanted numbers separated by space / space
    ==ExtractNumbers(A1," ") if you want them separated by a space

    Code:
    Function ExtractNumbers(strPhrase As String, strSeperator As String) As String
    
    Dim lngChars As Long
    Dim strChar As String, strNext As String
    Dim fNumber As Boolean
    Dim strReturns As String
    Dim lngChar As Long
    
    Application.Volatile
    
    lngChar = 1
    lngChars = Len(strPhrase)
    
    Do Until lngChar > lngChars
    	strChar = Mid(strPhrase, lngChar, 1)
    	If lngChar = lngChars Then
    		If IsNumeric(strChar) Then
    			strReturns = strReturns & strChar
    		End If
    	Else
    		strNext = Mid(strPhrase, lngChar + 1, 1)
    		If strNext <> "." And Not IsNumeric(strNext) And IsNumeric(strChar) Then
    			strReturns = strReturns & strChar & strSeperator
    		ElseIf strNext = "." And IsNumeric(strChar) Then
    			strReturns = strReturns & strChar & strNext
    		ElseIf IsNumeric(strChar) Then
    			strReturns = strReturns & strChar
    		End If
    	End If
    	lngChar = lngChar + 1
    Loop
    
    
    If strReturns <> "" And Right(strReturns, Len(strSeperator)) = strSeperator Then
    	strReturns = Left(strReturns, Len(strReturns) - Len(strSeperator))
    End If
    
    If Right(strReturns, 1) = "." Then
    	strReturns = Mid(strReturns, 1, Len(strReturns) - 1)
    End If
    
    ExtractNumbers = strReturns
    
    End Function
    Andrew

  9. #9
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks to everyone who contributed to this post.

Posting Permissions

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