Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheetfunctions
|
|||
|
|||
Extract Part of Sentence for Separate Cell's Value
Hello Gents/Ladies...another question....
I have a cell, C13, that says: Bonus: $4,000 (DEC06). My question: How can I copy just 4,000 from this cell and make K18 say $4,000. FYI, C13 does not always contain this data, just sometimes. I need something that would look for the dollar amount and copy only the dollar amount to K18. Thanks for all your help, I really appreciate your time. :o) Bull |
#2
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheetfunctions
|
|||
|
|||
Extract Part of Sentence for Separate Cell's Value
Look in the help index for MID and FIND
-- Don Guillett SalesAid Software wrote in message ps.com... Hello Gents/Ladies...another question.... I have a cell, C13, that says: Bonus: $4,000 (DEC06). My question: How can I copy just 4,000 from this cell and make K18 say $4,000. FYI, C13 does not always contain this data, just sometimes. I need something that would look for the dollar amount and copy only the dollar amount to K18. Thanks for all your help, I really appreciate your time. :o) Bull |
#3
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheetfunctions
|
|||
|
|||
Extract Part of Sentence for Separate Cell's Value
|
#5
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheetfunctions
|
|||
|
|||
Extract Part of Sentence for Separate Cell's Value
Thanks for the point in that direction. Appreciate it.
Don Guillett wrote: Look in the help index for MID and FIND -- Don Guillett SalesAid Software wrote in message ps.com... Hello Gents/Ladies...another question.... I have a cell, C13, that says: Bonus: $4,000 (DEC06). My question: How can I copy just 4,000 from this cell and make K18 say $4,000. FYI, C13 does not always contain this data, just sometimes. I need something that would look for the dollar amount and copy only the dollar amount to K18. Thanks for all your help, I really appreciate your time. :o) Bull |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Extract Part of Sentence for Separate Cell's Value
Tom, it would contain nothing. Thanks for your reply.
Tom Ogilvy wrote: if you mean sometimes it is empty or sometimes it isn't, then it would be =if(C13="","",--MID(C13,FIND("$",C13)+1,FIND(" ",C13, FIND("$",C13)+1-FIND("$",C13))-1)) If that isn't the case, then be specific about what you mean when you say sometimes it doesn't contain the data, i.e. what does it contain? -- Regards, Tom Ogilvy "Ron Rosenfeld" wrote: On 21 Dec 2006 06:54:54 -0800, wrote: Hello Gents/Ladies...another question.... I have a cell, C13, that says: Bonus: $4,000 (DEC06). My question: How can I copy just 4,000 from this cell and make K18 say $4,000. FYI, C13 does not always contain this data, just sometimes. I need something that would look for the dollar amount and copy only the dollar amount to K18. Thanks for all your help, I really appreciate your time. :o) Bull Your specifications are incomplete. In particular, you do not indicate what you wish to happen should the data you describe not be in the cell. To copy the value 4000 (not the text string 4,000) to cell K18 and "make K18 say" $4,000: 1. Format K18 as currency with zero decimal places. 2. Put the following formula in K18: =--MID(C13,FIND("$",C13)+1,FIND(" ",C13, FIND("$",C13)+1-FIND("$",C13))-1) This formula will give a VALUE error if C13 does not contain the appropriate data. But you'll have to tell us what you want to happen in that instance. --ron |
#7
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheetfunctions
|
|||
|
|||
Extract Part of Sentence for Separate Cell's Value
|
#8
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheetfunctions
|
|||
|
|||
Extract Part of Sentence for Separate Cell's Value
See function below to return value. Just call it by putting the formula
"=extractedvalue(c13)" in an empty cell. Function ExtractedValue(SubjectText As Variant) Dim CurrSymbol As String CurrSymbol = "$" Dim x, y, z As Integer Dim Exists, IsValue As Boolean Dim IntegerText As String Dim FractionText As String Dim IntegerPart As Variant Dim FractionPart As Variant IntegerText = "" 'check CurrSymbol exists in the phrase, if not exit with error message Exists = False For x = 1 To Len(SubjectText) If Mid(SubjectText, x, 1) = CurrSymbol Then Exists = True Else End If Next x If Exists = False Then ExtractedValue = CurrSymbol & " Not Found" Exit Function Else End If 'find the first occurance of currsymbol x = 1 While Mid(SubjectText, x, 1) < CurrSymbol x = x + 1 Wend 'throw away the first bit For y = x + 1 To Len(SubjectText) IntegerText = IntegerText & Mid(SubjectText, y, 1) Next y 'walk throught the IntegerText untile we run our of numbers. IsValue = True x = 1 While IsValue = True If _ Mid(IntegerText, x, 1) = 0 Or _ Mid(IntegerText, x, 1) = 1 Or _ Mid(IntegerText, x, 1) = 2 Or _ Mid(IntegerText, x, 1) = 3 Or _ Mid(IntegerText, x, 1) = 4 Or _ Mid(IntegerText, x, 1) = 5 Or _ Mid(IntegerText, x, 1) = 6 Or _ Mid(IntegerText, x, 1) = 7 Or _ Mid(IntegerText, x, 1) = 8 Or _ Mid(IntegerText, x, 1) = 9 Or _ Mid(IntegerText, x, 1) = "," Then Else IsValue = False End If x = x + 1 Wend IntegerPart = Left(IntegerText, x - 2) 'throw away the integer part bit For y = x - 1 To Len(IntegerText) FractionText = FractionText & Mid(IntegerText, y, 1) Next y 'find if next character is a "." If Left(FractionText, 1) = "." Then If _ Mid(FractionText, 2, 1) = 0 Or _ Mid(FractionText, 2, 1) = 1 Or _ Mid(FractionText, 2, 1) = 2 Or _ Mid(FractionText, 2, 1) = 3 Or _ Mid(FractionText, 2, 1) = 4 Or _ Mid(FractionText, 2, 1) = 5 Or _ Mid(FractionText, 2, 1) = 6 Or _ Mid(FractionText, 2, 1) = 7 Or _ Mid(FractionText, 2, 1) = 8 Or _ Mid(FractionText, 2, 1) = 9 Then 'walk through FractionText starting from 'the second character until the character is no varianter a number IsValue = True x = 2 While IsValue = True If _ Mid(FractionText, x, 1) = 0 Or _ Mid(FractionText, x, 1) = 1 Or _ Mid(FractionText, x, 1) = 2 Or _ Mid(FractionText, x, 1) = 3 Or _ Mid(FractionText, x, 1) = 4 Or _ Mid(FractionText, x, 1) = 5 Or _ Mid(FractionText, x, 1) = 6 Or _ Mid(FractionText, x, 1) = 7 Or _ Mid(FractionText, x, 1) = 8 Or _ Mid(FractionText, x, 1) = 9 Then Else IsValue = False End If x = x + 1 Wend FractionPart = Mid(FractionText, 2, x - 3) Else End If End If FractionPart = FractionPart / (10 ^ Len(FractionPart)) ExtractedValue = IntegerPart + FractionPart End Function wrote: Hello Gents/Ladies...another question.... I have a cell, C13, that says: Bonus: $4,000 (DEC06). My question: How can I copy just 4,000 from this cell and make K18 say $4,000. FYI, C13 does not always contain this data, just sometimes. I need something that would look for the dollar amount and copy only the dollar amount to K18. Thanks for all your help, I really appreciate your time. :o) Bull |
#9
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheetfunctions
|
|||
|
|||
Extract Part of Sentence for Separate Cell's Value
Perhaps a bit shorter:
============================== Option Explicit Function ParseVal(rg As Range) Dim objRe As Object Dim colMatches As Object Const Pattern As String = "\$([0-9,.]+)" Set objRe = CreateObject("vbscript.regexp") objRe.Global = True objRe.Pattern = Pattern If objRe.Test(rg.Text) = True Then Set colMatches = objRe.Execute(rg.Text) ParseVal = CDbl(colMatches(0)) Else ParseVal = "" End If End Function ============================ On 26 Dec 2006 09:26:45 -0800, "Ben" wrote: See function below to return value. Just call it by putting the formula "=extractedvalue(c13)" in an empty cell. Function ExtractedValue(SubjectText As Variant) Dim CurrSymbol As String CurrSymbol = "$" Dim x, y, z As Integer Dim Exists, IsValue As Boolean Dim IntegerText As String Dim FractionText As String Dim IntegerPart As Variant Dim FractionPart As Variant IntegerText = "" 'check CurrSymbol exists in the phrase, if not exit with error message Exists = False For x = 1 To Len(SubjectText) If Mid(SubjectText, x, 1) = CurrSymbol Then Exists = True Else End If Next x If Exists = False Then ExtractedValue = CurrSymbol & " Not Found" Exit Function Else End If 'find the first occurance of currsymbol x = 1 While Mid(SubjectText, x, 1) < CurrSymbol x = x + 1 Wend 'throw away the first bit For y = x + 1 To Len(SubjectText) IntegerText = IntegerText & Mid(SubjectText, y, 1) Next y 'walk throught the IntegerText untile we run our of numbers. IsValue = True x = 1 While IsValue = True If _ Mid(IntegerText, x, 1) = 0 Or _ Mid(IntegerText, x, 1) = 1 Or _ Mid(IntegerText, x, 1) = 2 Or _ Mid(IntegerText, x, 1) = 3 Or _ Mid(IntegerText, x, 1) = 4 Or _ Mid(IntegerText, x, 1) = 5 Or _ Mid(IntegerText, x, 1) = 6 Or _ Mid(IntegerText, x, 1) = 7 Or _ Mid(IntegerText, x, 1) = 8 Or _ Mid(IntegerText, x, 1) = 9 Or _ Mid(IntegerText, x, 1) = "," Then Else IsValue = False End If x = x + 1 Wend IntegerPart = Left(IntegerText, x - 2) 'throw away the integer part bit For y = x - 1 To Len(IntegerText) FractionText = FractionText & Mid(IntegerText, y, 1) Next y 'find if next character is a "." If Left(FractionText, 1) = "." Then If _ Mid(FractionText, 2, 1) = 0 Or _ Mid(FractionText, 2, 1) = 1 Or _ Mid(FractionText, 2, 1) = 2 Or _ Mid(FractionText, 2, 1) = 3 Or _ Mid(FractionText, 2, 1) = 4 Or _ Mid(FractionText, 2, 1) = 5 Or _ Mid(FractionText, 2, 1) = 6 Or _ Mid(FractionText, 2, 1) = 7 Or _ Mid(FractionText, 2, 1) = 8 Or _ Mid(FractionText, 2, 1) = 9 Then 'walk through FractionText starting from 'the second character until the character is no varianter a number IsValue = True x = 2 While IsValue = True If _ Mid(FractionText, x, 1) = 0 Or _ Mid(FractionText, x, 1) = 1 Or _ Mid(FractionText, x, 1) = 2 Or _ Mid(FractionText, x, 1) = 3 Or _ Mid(FractionText, x, 1) = 4 Or _ Mid(FractionText, x, 1) = 5 Or _ Mid(FractionText, x, 1) = 6 Or _ Mid(FractionText, x, 1) = 7 Or _ Mid(FractionText, x, 1) = 8 Or _ Mid(FractionText, x, 1) = 9 Then Else IsValue = False End If x = x + 1 Wend FractionPart = Mid(FractionText, 2, x - 3) Else End If End If FractionPart = FractionPart / (10 ^ Len(FractionPart)) ExtractedValue = IntegerPart + FractionPart End Function wrote: Hello Gents/Ladies...another question.... I have a cell, C13, that says: Bonus: $4,000 (DEC06). My question: How can I copy just 4,000 from this cell and make K18 say $4,000. FYI, C13 does not always contain this data, just sometimes. I need something that would look for the dollar amount and copy only the dollar amount to K18. Thanks for all your help, I really appreciate your time. :o) Bull --ron |
#10
Posted to microsoft.public.excel.programming,microsoft.public.excel.worksheetfunctions
|
|||
|
|||
Extract Part of Sentence for Separate Cell's Value
holy cow...Thanks a bunch guys...Take it easy.
Ron Rosenfeld wrote: Perhaps a bit shorter: ============================== Option Explicit Function ParseVal(rg As Range) Dim objRe As Object Dim colMatches As Object Const Pattern As String = "\$([0-9,.]+)" Set objRe = CreateObject("vbscript.regexp") objRe.Global = True objRe.Pattern = Pattern If objRe.Test(rg.Text) = True Then Set colMatches = objRe.Execute(rg.Text) ParseVal = CDbl(colMatches(0)) Else ParseVal = "" End If End Function ============================ On 26 Dec 2006 09:26:45 -0800, "Ben" wrote: See function below to return value. Just call it by putting the formula "=extractedvalue(c13)" in an empty cell. Function ExtractedValue(SubjectText As Variant) Dim CurrSymbol As String CurrSymbol = "$" Dim x, y, z As Integer Dim Exists, IsValue As Boolean Dim IntegerText As String Dim FractionText As String Dim IntegerPart As Variant Dim FractionPart As Variant IntegerText = "" 'check CurrSymbol exists in the phrase, if not exit with error message Exists = False For x = 1 To Len(SubjectText) If Mid(SubjectText, x, 1) = CurrSymbol Then Exists = True Else End If Next x If Exists = False Then ExtractedValue = CurrSymbol & " Not Found" Exit Function Else End If 'find the first occurance of currsymbol x = 1 While Mid(SubjectText, x, 1) < CurrSymbol x = x + 1 Wend 'throw away the first bit For y = x + 1 To Len(SubjectText) IntegerText = IntegerText & Mid(SubjectText, y, 1) Next y 'walk throught the IntegerText untile we run our of numbers. IsValue = True x = 1 While IsValue = True If _ Mid(IntegerText, x, 1) = 0 Or _ Mid(IntegerText, x, 1) = 1 Or _ Mid(IntegerText, x, 1) = 2 Or _ Mid(IntegerText, x, 1) = 3 Or _ Mid(IntegerText, x, 1) = 4 Or _ Mid(IntegerText, x, 1) = 5 Or _ Mid(IntegerText, x, 1) = 6 Or _ Mid(IntegerText, x, 1) = 7 Or _ Mid(IntegerText, x, 1) = 8 Or _ Mid(IntegerText, x, 1) = 9 Or _ Mid(IntegerText, x, 1) = "," Then Else IsValue = False End If x = x + 1 Wend IntegerPart = Left(IntegerText, x - 2) 'throw away the integer part bit For y = x - 1 To Len(IntegerText) FractionText = FractionText & Mid(IntegerText, y, 1) Next y 'find if next character is a "." If Left(FractionText, 1) = "." Then If _ Mid(FractionText, 2, 1) = 0 Or _ Mid(FractionText, 2, 1) = 1 Or _ Mid(FractionText, 2, 1) = 2 Or _ Mid(FractionText, 2, 1) = 3 Or _ Mid(FractionText, 2, 1) = 4 Or _ Mid(FractionText, 2, 1) = 5 Or _ Mid(FractionText, 2, 1) = 6 Or _ Mid(FractionText, 2, 1) = 7 Or _ Mid(FractionText, 2, 1) = 8 Or _ Mid(FractionText, 2, 1) = 9 Then 'walk through FractionText starting from 'the second character until the character is no varianter a number IsValue = True x = 2 While IsValue = True If _ Mid(FractionText, x, 1) = 0 Or _ Mid(FractionText, x, 1) = 1 Or _ Mid(FractionText, x, 1) = 2 Or _ Mid(FractionText, x, 1) = 3 Or _ Mid(FractionText, x, 1) = 4 Or _ Mid(FractionText, x, 1) = 5 Or _ Mid(FractionText, x, 1) = 6 Or _ Mid(FractionText, x, 1) = 7 Or _ Mid(FractionText, x, 1) = 8 Or _ Mid(FractionText, x, 1) = 9 Then Else IsValue = False End If x = x + 1 Wend FractionPart = Mid(FractionText, 2, x - 3) Else End If End If FractionPart = FractionPart / (10 ^ Len(FractionPart)) ExtractedValue = IntegerPart + FractionPart End Function wrote: Hello Gents/Ladies...another question.... I have a cell, C13, that says: Bonus: $4,000 (DEC06). My question: How can I copy just 4,000 from this cell and make K18 say $4,000. FYI, C13 does not always contain this data, just sometimes. I need something that would look for the dollar amount and copy only the dollar amount to K18. Thanks for all your help, I really appreciate your time. :o) Bull --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Replacing Part of a Cell's Content | Excel Worksheet Functions | |||
How I separate first two letters of the sentence in msexcel | Excel Discussion (Misc queries) | |||
Extract all letters from a cell sentence | Excel Worksheet Functions | |||
separate a Capital starting word from a sentence | Excel Worksheet Functions | |||
Extract the n word of a sentence in a cell | Excel Programming |