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

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

Extract Part of Sentence for Separate Cell's Value
 

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

Extract Part of Sentence for Separate Cell's Value
 

Got it Ron..if the data (i.e. Bonus: $4,000 (DEC06)) is not in the cell
then I would like K18 to say nothing or just ignore C13...I only want
K18 to have $4,000 or whatever dollar amount is listed in C13....

Is this what you mean?


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

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

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

Extract Part of Sentence for Separate Cell's Value
 

On 21 Dec 2006 07:38:44 -0800, wrote:

Got it Ron..if the data (i.e. Bonus: $4,000 (DEC06)) is not in the cell
then I would like K18 to say nothing or just ignore C13...I only want
K18 to have $4,000 or whatever dollar amount is listed in C13....

Is this what you mean?

Not exactly. However, you've already responded to Tom that C13 might be empty,
or might contain your text string including a dollar amount.

That being the case, he has supplied you with the appropriate modification of
the formula I supplied.


--ron

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

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

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