i have the following text string
"Home: installation on Cognos (5.5 hrs)" the number represents the number of
hours i want to have separated from the text" difficulty is the fact this can
be displayed as 5.5hr, or 5.5 uur. secondly this doesn't necessary is 3
positions. could also be 5 uur. the only common part is that the data is
ALWAYS at the right hand site of the text string

Try this:

=LOOKUP(10^10,--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"01234567 89")),ROW(INDIRECT("1:255"))))

If the cell is either empty or doesn't have a number in the string the
formula will return #N/A. Also, it will extract the *first* number it finds:

xxx 10.5 yy zzz 5.5

The result would be 10.5

Biff

"Jan" wrote in message
...
i have the following text string
"Home: installation on Cognos (5.5 hrs)" the number represents the number
of
hours i want to have separated from the text" difficulty is the fact this
can
be displayed as 5.5hr, or 5.5 uur. secondly this doesn't necessary is 3
positions. could also be 5 uur. the only common part is that the data is
ALWAYS at the right hand site of the text string

thanks, the result indicates i need to narrow my question. another conditon
would be the number to be represented must meet the condition captured
between brackets as it now also present general numbers.

"T. Valko" wrote:

Try this:

=LOOKUP(10^10,--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"01234567 89")),ROW(INDIRECT("1:255"))))

If the cell is either empty or doesn't have a number in the string the
formula will return #N/A. Also, it will extract the *first* number it finds:

xxx 10.5 yy zzz 5.5

The result would be 10.5

Biff

"Jan" wrote in message
...
i have the following text string
"Home: installation on Cognos (5.5 hrs)" the number represents the number
of
hours i want to have separated from the text" difficulty is the fact this
can
be displayed as 5.5hr, or 5.5 uur. secondly this doesn't necessary is 3
positions. could also be 5 uur. the only common part is that the data is
ALWAYS at the right hand site of the text string

Post *several representative samples* and tell us what the results for each
sample should be.

Biff

"Jan" wrote in message
...
thanks, the result indicates i need to narrow my question. another
conditon
would be the number to be represented must meet the condition captured
between brackets as it now also present general numbers.

"T. Valko" wrote:

Try this:

=LOOKUP(10^10,--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"01234567 89")),ROW(INDIRECT("1:255"))))

If the cell is either empty or doesn't have a number in the string the
formula will return #N/A. Also, it will extract the *first* number it
finds:

xxx 10.5 yy zzz 5.5

The result would be 10.5

Biff

"Jan" wrote in message
...
i have the following text string
"Home: installation on Cognos (5.5 hrs)" the number represents the
number
of
hours i want to have separated from the text" difficulty is the fact
this
can
be displayed as 5.5hr, or 5.5 uur. secondly this doesn't necessary is 3
positions. could also be 5 uur. the only common part is that the data
is
ALWAYS at the right hand site of the text string

This worked for me since there were only a pair of ()'s (and those ()'s had to
be there).

I copied the column to the next column (inserting that extra column first).

I selected the column that will hold the text.
Edit|Replace
what: _(* I used _ to represent the space character)
with: (leave blank)
replace all

Then I selected the column that would hold the numbers.
Edit|Replace
what: *(
with: (leave blank)
replace all

and one more

Edit|replace
what: _*)*
with: (leave blank)
replace all




Jan wrote:

i have the following text string
"Home: installation on Cognos (5.5 hrs)" the number represents the number of
hours i want to have separated from the text" difficulty is the fact this can
be displayed as 5.5hr, or 5.5 uur. secondly this doesn't necessary is 3
positions. could also be 5 uur. the only common part is that the data is
ALWAYS at the right hand site of the text string

--

Dave Peterson

example a) Site: 3480 test data migration
example b) Home: Porterbrook / Ravers investigation (2 uur)
example c) Home: installation on Cognos (5.5 hrs)

result a) should be 0 but prints 3480
result b) should prints 2 which is OK
result c) should prints 5.5 which is ok

a) numbers could be anywhere in the text string however as they are not
between brackets ( ) they should be ignored.

hope this helps


"T. Valko" wrote:

Post *several representative samples* and tell us what the results for each
sample should be.

Biff

"Jan" wrote in message
...
thanks, the result indicates i need to narrow my question. another
conditon
would be the number to be represented must meet the condition captured
between brackets as it now also present general numbers.

"T. Valko" wrote:

Try this:

=LOOKUP(10^10,--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"01234567 89")),ROW(INDIRECT("1:255"))))

If the cell is either empty or doesn't have a number in the string the
formula will return #N/A. Also, it will extract the *first* number it
finds:

xxx 10.5 yy zzz 5.5

The result would be 10.5

Biff

"Jan" wrote in message
...
i have the following text string
"Home: installation on Cognos (5.5 hrs)" the number represents the
number
of
hours i want to have separated from the text" difficulty is the fact
this
can
be displayed as 5.5hr, or 5.5 uur. secondly this doesn't necessary is 3
positions. could also be 5 uur. the only common part is that the data
is
ALWAYS at the right hand site of the text string

You interested in a macro solution?

Sub RemoveAlphas()
' Remove alpha characters from a string.
' except for decimal points
Dim intI As Integer
Dim rngR As Range, rngRR As Range
Dim strNotNum As String, strTemp As String

Set rngRR = Selection.SpecialCells(xlCellTypeConstants, _
xlTextValues)

For Each rngR In rngRR
strTemp = ""
For intI = 1 To Len(rngR.Value)
If Mid(rngR.Value, intI, 1) Like "[0-9,.]" Then
strNotNum = Mid(rngR.Value, intI, 1)
Else: strNotNum = ""
End If
strTemp = strTemp & strNotNum
Next intI
rngR.Value = strTemp
Next rngR

End Sub


Gord Dibben MS Excel MVP

On Fri, 1 Jun 2007 14:41:01 -0700, Jan wrote:

thanks, the result indicates i need to narrow my question. another conditon
would be the number to be represented must meet the condition captured
between brackets as it now also present general numbers.

"T. Valko" wrote:

Try this:

=LOOKUP(10^10,--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"01234567 89")),ROW(INDIRECT("1:255"))))

If the cell is either empty or doesn't have a number in the string the
formula will return #N/A. Also, it will extract the *first* number it finds:

xxx 10.5 yy zzz 5.5

The result would be 10.5

Biff

"Jan" wrote in message
...
i have the following text string
"Home: installation on Cognos (5.5 hrs)" the number represents the number
of
hours i want to have separated from the text" difficulty is the fact this
can
be displayed as 5.5hr, or 5.5 uur. secondly this doesn't necessary is 3
positions. could also be 5 uur. the only common part is that the data is
ALWAYS at the right hand site of the text string

Ok, this assumes that the only time ( ) appears in the string is with
numbers enclosed:

=IF(AND(ISNUMBER(FIND({"(",")"},A1))),LOOKUP(10^10 ,--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"01234567 89")),ROW(INDIRECT("1:255")))),"")

Biff

"Jan" wrote in message
...
example a) Site: 3480 test data migration
example b) Home: Porterbrook / Ravers investigation (2 uur)
example c) Home: installation on Cognos (5.5 hrs)

result a) should be 0 but prints 3480
result b) should prints 2 which is OK
result c) should prints 5.5 which is ok

a) numbers could be anywhere in the text string however as they are not
between brackets ( ) they should be ignored.

hope this helps


"T. Valko" wrote:

Post *several representative samples* and tell us what the results for
each
sample should be.

Biff

"Jan" wrote in message
...
thanks, the result indicates i need to narrow my question. another
conditon
would be the number to be represented must meet the condition captured
between brackets as it now also present general numbers.

"T. Valko" wrote:

Try this:

=LOOKUP(10^10,--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"01234567 89")),ROW(INDIRECT("1:255"))))

If the cell is either empty or doesn't have a number in the string the
formula will return #N/A. Also, it will extract the *first* number it
finds:

xxx 10.5 yy zzz 5.5

The result would be 10.5

Biff

"Jan" wrote in message
...
i have the following text string
"Home: installation on Cognos (5.5 hrs)" the number represents the
number
of
hours i want to have separated from the text" difficulty is the fact
this
can
be displayed as 5.5hr, or 5.5 uur. secondly this doesn't necessary
is 3
positions. could also be 5 uur. the only common part is that the
data
is
ALWAYS at the right hand site of the text string

hi, i tried the macro however all numbers are printed, i would go for such a
solution as T Valko created as this can be placed in a separate column which
gives great performance. the macro has less performance, i have to check
about 30.000 rows.

thanks

"Dave Peterson" wrote:

This worked for me since there were only a pair of ()'s (and those ()'s had to
be there).

I copied the column to the next column (inserting that extra column first).

I selected the column that will hold the text.
Edit|Replace
what: _(* I used _ to represent the space character)
with: (leave blank)
replace all

Then I selected the column that would hold the numbers.
Edit|Replace
what: *(
with: (leave blank)
replace all

and one more

Edit|replace
what: _*)*
with: (leave blank)
replace all




Jan wrote:

i have the following text string
"Home: installation on Cognos (5.5 hrs)" the number represents the number of
hours i want to have separated from the text" difficulty is the fact this can
be displayed as 5.5hr, or 5.5 uur. secondly this doesn't necessary is 3
positions. could also be 5 uur. the only common part is that the data is
ALWAYS at the right hand site of the text string

--

Dave Peterson

The macro may not work for you, but I know that the technique that I suggested
will fail. It would work on the data that you originally posted, but not on
some of the data in your subsequent post.

It probably would have been quicker (for you and Biff!) for you to have posted a
representative sample of data--like you did later.

This surely doesn't help this time <bg, but maybe it'll help the next time
(either you or someone lurking before asking a question).

Jan wrote:

hi, i tried the macro however all numbers are printed, i would go for such a
solution as T Valko created as this can be placed in a separate column which
gives great performance. the macro has less performance, i have to check
about 30.000 rows.

thanks

"Dave Peterson" wrote:

This worked for me since there were only a pair of ()'s (and those ()'s had to
be there).

I copied the column to the next column (inserting that extra column first).

I selected the column that will hold the text.
Edit|Replace
what: _(* I used _ to represent the space character)
with: (leave blank)
replace all

Then I selected the column that would hold the numbers.
Edit|Replace
what: *(
with: (leave blank)
replace all

and one more

Edit|replace
what: _*)*
with: (leave blank)
replace all




Jan wrote:

i have the following text string
"Home: installation on Cognos (5.5 hrs)" the number represents the number of
hours i want to have separated from the text" difficulty is the fact this can
be displayed as 5.5hr, or 5.5 uur. secondly this doesn't necessary is 3
positions. could also be 5 uur. the only common part is that the data is
ALWAYS at the right hand site of the text string

--

Dave Peterson


--

Dave Peterson

This one's a few keystrokes shorter:

=IF(AND(ISNUMBER(FIND({"(",")"},A1))),
--LEFT(MID(A1,FIND("(",A1)+1,255),
FIND(" ",MID(A1,FIND("(",A1)+1,255))-1),"")

Biff

"T. Valko" wrote in message
...
Ok, this assumes that the only time ( ) appears in the string is with
numbers enclosed:

=IF(AND(ISNUMBER(FIND({"(",")"},A1))),LOOKUP(10^10 ,--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"01234567 89")),ROW(INDIRECT("1:255")))),"")

Biff

"Jan" wrote in message
...
example a) Site: 3480 test data migration
example b) Home: Porterbrook / Ravers investigation (2 uur)
example c) Home: installation on Cognos (5.5 hrs)

result a) should be 0 but prints 3480
result b) should prints 2 which is OK
result c) should prints 5.5 which is ok

a) numbers could be anywhere in the text string however as they are not
between brackets ( ) they should be ignored.

hope this helps


"T. Valko" wrote:

Post *several representative samples* and tell us what the results for
each
sample should be.

Biff

"Jan" wrote in message
...
thanks, the result indicates i need to narrow my question. another
conditon
would be the number to be represented must meet the condition captured
between brackets as it now also present general numbers.

"T. Valko" wrote:

Try this:

=LOOKUP(10^10,--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"01234567 89")),ROW(INDIRECT("1:255"))))

If the cell is either empty or doesn't have a number in the string
the
formula will return #N/A. Also, it will extract the *first* number it
finds:

xxx 10.5 yy zzz 5.5

The result would be 10.5

Biff

"Jan" wrote in message
...
i have the following text string
"Home: installation on Cognos (5.5 hrs)" the number represents the
number
of
hours i want to have separated from the text" difficulty is the
fact
this
can
be displayed as 5.5hr, or 5.5 uur. secondly this doesn't necessary
is 3
positions. could also be 5 uur. the only common part is that the
data
is
ALWAYS at the right hand site of the text string

hi, i tried the macro however all numbers are printed, i would go for such
a
solution as T Valko created as this can be placed in a separate column
which
gives great performance. the macro has less performance, i have to check
about 30.000 rows.

Give this macro function a try...

Function ExtractNumber(ByVal StringIn As String) As Single
On Error Resume Next
StringIn = Replace(StringIn, ")", " )")
ExtractNumber = CSng(Split(Trim$(Split(StringIn, "(")(1)))(0))
End Function

As long as the String value you feed into it has the number you want within
parentheses (what you call brackets) and the number is followed by either a
space or a closing parenthesis; that is, your number can't look like this...
(5.5hrs) where there is no space between the number and any numeric
description, then the function will return the number (as a Single, but you
can change that if you wish).

Rick

biff, based on the formula i found my original description is incorrect,
sorry but i overlooked them cause of the number of records. on top of the
earlier given examples below the other exeptions.


Example d) HOME upgrade laptop to pathc 08 (4hr)
Example e) voorbereiden upload sheets training (is uiteindelijk niet
doorgegaan)
Example f) Helvoet aanmaken trainingsomgeving (CDE) 2 hr
Example g) netwerk problemen ( 3 hr)
Example h) WBGR - installation support (multi org)using email; 1 uur
Example i) new years celebration (Lunch) 3 hrs

Example j) 1) Nutricia Zoetermeer (migration to 8.2)

Example k) 2) Siemens (prospect) RFP (request Bert)
Example l) EMW shipment PRORDF modifications (DHA onsite) (1 uur)

Result d) should print 4
Result e) should print 0
Result f) should print 2
Result g) should print 3
Result h) should print 1
Result i) should print 3
Result j) should print 0
Result k) should print 0
Result l) should print 1

thanks.

"T. Valko" wrote:

Post *several representative samples* and tell us what the results for each
sample should be.

Biff

"Jan" wrote in message
...
thanks, the result indicates i need to narrow my question. another
conditon
would be the number to be represented must meet the condition captured
between brackets as it now also present general numbers.

"T. Valko" wrote:

Try this:

=LOOKUP(10^10,--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"01234567 89")),ROW(INDIRECT("1:255"))))

If the cell is either empty or doesn't have a number in the string the
formula will return #N/A. Also, it will extract the *first* number it
finds:

xxx 10.5 yy zzz 5.5

The result would be 10.5

Biff

"Jan" wrote in message
...
i have the following text string
"Home: installation on Cognos (5.5 hrs)" the number represents the
number
of
hours i want to have separated from the text" difficulty is the fact
this
can
be displayed as 5.5hr, or 5.5 uur. secondly this doesn't necessary is 3
positions. could also be 5 uur. the only common part is that the data
is
ALWAYS at the right hand site of the text string

Dave, you are correct. the file however contains thousands of rows, i should
have investigated better myself. thanks

"Dave Peterson" wrote:

The macro may not work for you, but I know that the technique that I suggested
will fail. It would work on the data that you originally posted, but not on
some of the data in your subsequent post.

It probably would have been quicker (for you and Biff!) for you to have posted a
representative sample of data--like you did later.

This surely doesn't help this time <bg, but maybe it'll help the next time
(either you or someone lurking before asking a question).

Jan wrote:

hi, i tried the macro however all numbers are printed, i would go for such a
solution as T Valko created as this can be placed in a separate column which
gives great performance. the macro has less performance, i have to check
about 30.000 rows.

thanks

"Dave Peterson" wrote:

This worked for me since there were only a pair of ()'s (and those ()'s had to
be there).

I copied the column to the next column (inserting that extra column first).

I selected the column that will hold the text.
Edit|Replace
what: _(* I used _ to represent the space character)
with: (leave blank)
replace all

Then I selected the column that would hold the numbers.
Edit|Replace
what: *(
with: (leave blank)
replace all

and one more

Edit|replace
what: _*)*
with: (leave blank)
replace all




Jan wrote:

i have the following text string
"Home: installation on Cognos (5.5 hrs)" the number represents the number of
hours i want to have separated from the text" difficulty is the fact this can
be displayed as 5.5hr, or 5.5 uur. secondly this doesn't necessary is 3
positions. could also be 5 uur. the only common part is that the data is
ALWAYS at the right hand site of the text string

--

Dave Peterson


--

Dave Peterson

example a) Site: 3480 test data migration
example b) Home: Porterbrook / Ravers investigation (2 uur)
example c) Home: installation on Cognos (5.5 hrs)

result a) should be 0 but prints 3480
result b) should prints 2 which is OK
result c) should prints 5.5 which is ok

biff, based on the formula i found my original description is incorrect,
sorry but i overlooked them cause of the number of records. on top of the
earlier given examples below the other exeptions.


Example d) HOME upgrade laptop to pathc 08 (4hr)
Example e) voorbereiden upload sheets training (is uiteindelijk niet
doorgegaan)
Example f) Helvoet aanmaken trainingsomgeving (CDE) 2 hr
Example g) netwerk problemen ( 3 hr)
Example h) WBGR - installation support (multi org)using email; 1 uur
Example i) new years celebration (Lunch) 3 hrs

Example j) 1) Nutricia Zoetermeer (migration to 8.2)

Example k) 2) Siemens (prospect) RFP (request Bert)
Example l) EMW shipment PRORDF modifications (DHA onsite) (1 uur)

Result d) should print 4
Result e) should print 0
Result f) should print 2
Result g) should print 3
Result h) should print 1
Result i) should print 3
Result j) should print 0
Result k) should print 0
Result l) should print 1

Your new examples creates a problem in your specification that will need
clarifying. In examples (f) and (i), you say you want the 2 and 3... what
distinguishes (f) and (i) from (a) where you also had a number without
surrounding parentheses? Looking at example (j), it seems like it might be
because the numbers have descriptive text abbreviations after them. If that
is so, please tell us ALL of the abbreviations that will make a number one
of the numbers you want to retrieve. Another question... are the numbers you
want (the ones with the abbreviations after them) ALWAYS at the end of the
data line (or can other text, aside from the abbreviation) follow them?

Rick

"Rick Rothstein (MVP - VB)" wrote:

example a) Site: 3480 test data migration
example b) Home: Porterbrook / Ravers investigation (2 uur)
example c) Home: installation on Cognos (5.5 hrs)

result a) should be 0 but prints 3480
result b) should prints 2 which is OK
result c) should prints 5.5 which is ok

biff, based on the formula i found my original description is incorrect,
sorry but i overlooked them cause of the number of records. on top of the
earlier given examples below the other exeptions.


Example d) HOME upgrade laptop to pathc 08 (4hr)
Example e) voorbereiden upload sheets training (is uiteindelijk niet
doorgegaan)
Example f) Helvoet aanmaken trainingsomgeving (CDE) 2 hr
Example g) netwerk problemen ( 3 hr)
Example h) WBGR - installation support (multi org)using email; 1 uur
Example i) new years celebration (Lunch) 3 hrs

Example j) 1) Nutricia Zoetermeer (migration to 8.2)

Example k) 2) Siemens (prospect) RFP (request Bert)
Example l) EMW shipment PRORDF modifications (DHA onsite) (1 uur)

Result d) should print 4
Result e) should print 0
Result f) should print 2
Result g) should print 3
Result h) should print 1
Result i) should print 3
Result j) should print 0
Result k) should print 0
Result l) should print 1

Your new examples creates a problem in your specification that will need
clarifying. In examples (f) and (i), you say you want the 2 and 3... what
distinguishes (f) and (i) from (a) where you also had a number without
surrounding parentheses? Looking at example (j), it seems like it might be
because the numbers have descriptive text abbreviations after them. If that
is so, please tell us ALL of the abbreviations that will make a number one
of the numbers you want to retrieve. Another question... are the numbers you
want (the ones with the abbreviations after them) ALWAYS at the end of the
data line (or can other text, aside from the abbreviation) follow them?

Rick

1) Example a is just a number in the string, all number one numbers are
always at the end of the string, but not always between parentheses. I also
found situation with a space between the opening parentheses and the number.
2) Below are the formats I could expect. The abbreviations represent worked
hours in English and Dutch.

X hr
X hrs
X uur

Xhr
Xhrs
Xuur

x.x hr
x.x hrs
x.x uur

x.xhr
x.xhrs
x.xuur

hopes this answer the question?

"Rick Rothstein (MVP - VB)" wrote:

example a) Site: 3480 test data migration
example b) Home: Porterbrook / Ravers investigation (2 uur)
example c) Home: installation on Cognos (5.5 hrs)

result a) should be 0 but prints 3480
result b) should prints 2 which is OK
result c) should prints 5.5 which is ok

biff, based on the formula i found my original description is incorrect,
sorry but i overlooked them cause of the number of records. on top of the
earlier given examples below the other exeptions.


Example d) HOME upgrade laptop to pathc 08 (4hr)
Example e) voorbereiden upload sheets training (is uiteindelijk niet
doorgegaan)
Example f) Helvoet aanmaken trainingsomgeving (CDE) 2 hr
Example g) netwerk problemen ( 3 hr)
Example h) WBGR - installation support (multi org)using email; 1 uur
Example i) new years celebration (Lunch) 3 hrs

Example j) 1) Nutricia Zoetermeer (migration to 8.2)

Example k) 2) Siemens (prospect) RFP (request Bert)
Example l) EMW shipment PRORDF modifications (DHA onsite) (1 uur)

Result d) should print 4
Result e) should print 0
Result f) should print 2
Result g) should print 3
Result h) should print 1
Result i) should print 3
Result j) should print 0
Result k) should print 0
Result l) should print 1

Your new examples creates a problem in your specification that will need
clarifying. In examples (f) and (i), you say you want the 2 and 3... what
distinguishes (f) and (i) from (a) where you also had a number without
surrounding parentheses? Looking at example (j), it seems like it might be
because the numbers have descriptive text abbreviations after them. If that
is so, please tell us ALL of the abbreviations that will make a number one
of the numbers you want to retrieve. Another question... are the numbers you
want (the ones with the abbreviations after them) ALWAYS at the end of the
data line (or can other text, aside from the abbreviation) follow them?

Rick

Well, with all these different conditions I think we're beyond using
built-in functions.

Maybe Ron Rosenfeld will drop in with a regex solution.

Biff

"Jan" wrote in message
...
1) Example a is just a number in the string, all number one numbers are
always at the end of the string, but not always between parentheses. I
also
found situation with a space between the opening parentheses and the
number.
2) Below are the formats I could expect. The abbreviations represent
worked
hours in English and Dutch.

X hr
X hrs
X uur

Xhr
Xhrs
Xuur

x.x hr
x.x hrs
x.x uur

x.xhr
x.xhrs
x.xuur

hopes this answer the question?

"Rick Rothstein (MVP - VB)" wrote:

example a) Site: 3480 test data migration
example b) Home: Porterbrook / Ravers investigation (2 uur)
example c) Home: installation on Cognos (5.5 hrs)

result a) should be 0 but prints 3480
result b) should prints 2 which is OK
result c) should prints 5.5 which is ok

biff, based on the formula i found my original description is
incorrect,
sorry but i overlooked them cause of the number of records. on top of
the
earlier given examples below the other exeptions.


Example d) HOME upgrade laptop to pathc 08 (4hr)
Example e) voorbereiden upload sheets training (is uiteindelijk niet
doorgegaan)
Example f) Helvoet aanmaken trainingsomgeving (CDE) 2 hr
Example g) netwerk problemen ( 3 hr)
Example h) WBGR - installation support (multi org)using email; 1 uur
Example i) new years celebration (Lunch) 3 hrs

Example j) 1) Nutricia Zoetermeer (migration to 8.2)

Example k) 2) Siemens (prospect) RFP (request Bert)
Example l) EMW shipment PRORDF modifications (DHA onsite) (1 uur)

Result d) should print 4
Result e) should print 0
Result f) should print 2
Result g) should print 3
Result h) should print 1
Result i) should print 3
Result j) should print 0
Result k) should print 0
Result l) should print 1

Your new examples creates a problem in your specification that will need
clarifying. In examples (f) and (i), you say you want the 2 and 3... what
distinguishes (f) and (i) from (a) where you also had a number without
surrounding parentheses? Looking at example (j), it seems like it might
be
because the numbers have descriptive text abbreviations after them. If
that
is so, please tell us ALL of the abbreviations that will make a number
one
of the numbers you want to retrieve. Another question... are the numbers
you
want (the ones with the abbreviations after them) ALWAYS at the end of
the
data line (or can other text, aside from the abbreviation) follow them?

Rick

This UDF, which can be called from the spreadsheet, returns the number immediatly preceeding either "hr" or "urr".

It returns the desired values of the examples given.

Biff, for now i would like to retreive the majority of the numbers. could you
describe how i should midify the formulae to retreive hours with the folowing
format ( x hr) or (xhr) the rest i will have to do manualy.

"T. Valko" wrote:

Well, with all these different conditions I think we're beyond using
built-in functions.

Maybe Ron Rosenfeld will drop in with a regex solution.

Biff

"Jan" wrote in message
...
1) Example a is just a number in the string, all number one numbers are
always at the end of the string, but not always between parentheses. I
also
found situation with a space between the opening parentheses and the
number.
2) Below are the formats I could expect. The abbreviations represent
worked
hours in English and Dutch.

X hr
X hrs
X uur

Xhr
Xhrs
Xuur

x.x hr
x.x hrs
x.x uur

x.xhr
x.xhrs
x.xuur

hopes this answer the question?

"Rick Rothstein (MVP - VB)" wrote:

example a) Site: 3480 test data migration
example b) Home: Porterbrook / Ravers investigation (2 uur)
example c) Home: installation on Cognos (5.5 hrs)

result a) should be 0 but prints 3480
result b) should prints 2 which is OK
result c) should prints 5.5 which is ok

biff, based on the formula i found my original description is
incorrect,
sorry but i overlooked them cause of the number of records. on top of
the
earlier given examples below the other exeptions.


Example d) HOME upgrade laptop to pathc 08 (4hr)
Example e) voorbereiden upload sheets training (is uiteindelijk niet
doorgegaan)
Example f) Helvoet aanmaken trainingsomgeving (CDE) 2 hr
Example g) netwerk problemen ( 3 hr)
Example h) WBGR - installation support (multi org)using email; 1 uur
Example i) new years celebration (Lunch) 3 hrs

Example j) 1) Nutricia Zoetermeer (migration to 8.2)

Example k) 2) Siemens (prospect) RFP (request Bert)
Example l) EMW shipment PRORDF modifications (DHA onsite) (1 uur)

Result d) should print 4
Result e) should print 0
Result f) should print 2
Result g) should print 3
Result h) should print 1
Result i) should print 3
Result j) should print 0
Result k) should print 0
Result l) should print 1

Your new examples creates a problem in your specification that will need
clarifying. In examples (f) and (i), you say you want the 2 and 3... what
distinguishes (f) and (i) from (a) where you also had a number without
surrounding parentheses? Looking at example (j), it seems like it might
be
because the numbers have descriptive text abbreviations after them. If
that
is so, please tell us ALL of the abbreviations that will make a number
one
of the numbers you want to retrieve. Another question... are the numbers
you
want (the ones with the abbreviations after them) ALWAYS at the end of
the
data line (or can other text, aside from the abbreviation) follow them?

Rick

example a) Site: 3480 test data migration
example b) Home: Porterbrook / Ravers investigation (2 uur)
example c) Home: installation on Cognos (5.5 hrs)

result a) should be 0 but prints 3480
result b) should prints 2 which is OK
result c) should prints 5.5 which is ok

biff, based on the formula i found my original description is
incorrect,
sorry but i overlooked them cause of the number of records. on top of
the
earlier given examples below the other exeptions.


Example d) HOME upgrade laptop to pathc 08 (4hr)
Example e) voorbereiden upload sheets training (is uiteindelijk niet
doorgegaan)
Example f) Helvoet aanmaken trainingsomgeving (CDE) 2 hr
Example g) netwerk problemen ( 3 hr)
Example h) WBGR - installation support (multi org)using email; 1 uur
Example i) new years celebration (Lunch) 3 hrs

Example j) 1) Nutricia Zoetermeer (migration to 8.2)

Example k) 2) Siemens (prospect) RFP (request Bert)
Example l) EMW shipment PRORDF modifications (DHA onsite) (1 uur)

Result d) should print 4
Result e) should print 0
Result f) should print 2
Result g) should print 3
Result h) should print 1
Result i) should print 3
Result j) should print 0
Result k) should print 0
Result l) should print 1

Your new examples creates a problem in your specification that will need
clarifying. In examples (f) and (i), you say you want the 2 and 3... what
distinguishes (f) and (i) from (a) where you also had a number without
surrounding parentheses? Looking at example (j), it seems like it might
be
because the numbers have descriptive text abbreviations after them. If
that
is so, please tell us ALL of the abbreviations that will make a number
one
of the numbers you want to retrieve. Another question... are the numbers
you
want (the ones with the abbreviations after them) ALWAYS at the end of
the
data line (or can other text, aside from the abbreviation) follow them?

1) Example a is just a number in the string, all number one numbers are
always at the end of the string, but not always between parentheses. I
also
found situation with a space between the opening parentheses and the
number.
2) Below are the formats I could expect. The abbreviations represent
worked
hours in English and Dutch.

X hr
X hrs
X uur

Xhr
Xhrs
Xuur

x.x hr
x.x hrs
x.x uur

x.xhr
x.xhrs
x.xuur

hopes this answer the question?

I think this macro function will extract the numbers you are looking for...

Function ExtractNumber(ByVal StringIn As String) As Single
Dim Extraction() As String
On Error Resume Next
StringIn = Replace(StringIn, "(", "( ")
StringIn = Replace(StringIn, "hr", Chr$(1))
StringIn = Replace(StringIn, "uur", Chr$(1))
Extraction = Split(StringIn, Chr$(1))
Extraction = Split(RTrim$(Extraction(UBound(Extraction) - 1)), " ")
ExtractNumber = CSng(Extraction(UBound(Extraction)))
End Function


Rick

With A1:A9 containing these values:

Site: 3480 test data migration
Home: Porterbrook / Ravers investigation (2 uur)
Home: installation on Cognos (5.5 hrs)
HOME upgrade laptop to pathc 08 (4hr)
voorbereiden upload sheets training (is uiteindelijk niet doorgegaan)
Helvoet aanmaken trainingsomgeving (CDE) 2 hr
netwerk problemen ( 3 hr)
WBGR - installation support (multi org)using email; 1 uur
new years celebration (Lunch) 3 hrs

Try something like this:

Using Col_B as a "helper column":
B1: =LEFT(A1,LEN(A1)-MAX(INDEX(COUNTIF(A1,"*"&$F$2:$F$13)*$G$2:$G$13,0) )+1)

Copy B1 down through B9

This formula pulls the numbers from the end of those values:
C1: =IF(ISNUMBER(--MID(B1,MAX(FIND({"~","("},SUBSTITUTE(B1,"
","~",LEN(B1)-LEN(SUBSTITUTE(B1,"
","")))))+1,255)),--MID(B1,MAX(FIND({"~","("},SUBSTITUTE(B1,"
","~",LEN(B1)-LEN(SUBSTITUTE(B1," ","")))))+1,255),"na")

Copy C1 down through C9
(Note: text wrap may adversely impact the display)

Using those formulas, C1:C9 returns these values:

na
2
5.5
4
na
na
3
1
3

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Jan" wrote:

biff, based on the formula i found my original description is incorrect,
sorry but i overlooked them cause of the number of records. on top of the
earlier given examples below the other exeptions.


Example d) HOME upgrade laptop to pathc 08 (4hr)
Example e) voorbereiden upload sheets training (is uiteindelijk niet
doorgegaan)
Example f) Helvoet aanmaken trainingsomgeving (CDE) 2 hr
Example g) netwerk problemen ( 3 hr)
Example h) WBGR - installation support (multi org)using email; 1 uur
Example i) new years celebration (Lunch) 3 hrs

Example j) 1) Nutricia Zoetermeer (migration to 8.2)

Example k) 2) Siemens (prospect) RFP (request Bert)
Example l) EMW shipment PRORDF modifications (DHA onsite) (1 uur)

Result d) should print 4
Result e) should print 0
Result f) should print 2
Result g) should print 3
Result h) should print 1
Result i) should print 3
Result j) should print 0
Result k) should print 0
Result l) should print 1

thanks.

"T. Valko" wrote:

Post *several representative samples* and tell us what the results for each
sample should be.

Biff

"Jan" wrote in message
...
thanks, the result indicates i need to narrow my question. another
conditon
would be the number to be represented must meet the condition captured
between brackets as it now also present general numbers.

"T. Valko" wrote:

Try this:

=LOOKUP(10^10,--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"01234567 89")),ROW(INDIRECT("1:255"))))

If the cell is either empty or doesn't have a number in the string the
formula will return #N/A. Also, it will extract the *first* number it
finds:

xxx 10.5 yy zzz 5.5

The result would be 10.5

Biff

"Jan" wrote in message
...
i have the following text string
"Home: installation on Cognos (5.5 hrs)" the number represents the
number
of
hours i want to have separated from the text" difficulty is the fact
this
can
be displayed as 5.5hr, or 5.5 uur. secondly this doesn't necessary is 3
positions. could also be 5 uur. the only common part is that the data
is
ALWAYS at the right hand site of the text string

OOPS! I forgot to post a significant part.....

Cells E2:F13 contain these values:

hr 5
hr 4
hr) 4
hr) 5
hrs 4
hrs 5
hrs) 5
hrs) 6
uur 4
uur 5
uur) 5
uur) 6

Note that some of the text begins with a space.

***********
Regards,
Ron

XL2002, WinXP


"Jan" wrote:

biff, based on the formula i found my original description is incorrect,
sorry but i overlooked them cause of the number of records. on top of the
earlier given examples below the other exeptions.


Example d) HOME upgrade laptop to pathc 08 (4hr)
Example e) voorbereiden upload sheets training (is uiteindelijk niet
doorgegaan)
Example f) Helvoet aanmaken trainingsomgeving (CDE) 2 hr
Example g) netwerk problemen ( 3 hr)
Example h) WBGR - installation support (multi org)using email; 1 uur
Example i) new years celebration (Lunch) 3 hrs

Example j) 1) Nutricia Zoetermeer (migration to 8.2)

Example k) 2) Siemens (prospect) RFP (request Bert)
Example l) EMW shipment PRORDF modifications (DHA onsite) (1 uur)

Result d) should print 4
Result e) should print 0
Result f) should print 2
Result g) should print 3
Result h) should print 1
Result i) should print 3
Result j) should print 0
Result k) should print 0
Result l) should print 1

thanks.

"T. Valko" wrote:

Post *several representative samples* and tell us what the results for each
sample should be.

Biff

"Jan" wrote in message
...
thanks, the result indicates i need to narrow my question. another
conditon
would be the number to be represented must meet the condition captured
between brackets as it now also present general numbers.

"T. Valko" wrote:

Try this:

=LOOKUP(10^10,--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"01234567 89")),ROW(INDIRECT("1:255"))))

If the cell is either empty or doesn't have a number in the string the
formula will return #N/A. Also, it will extract the *first* number it
finds:

xxx 10.5 yy zzz 5.5

The result would be 10.5

Biff

"Jan" wrote in message
...
i have the following text string
"Home: installation on Cognos (5.5 hrs)" the number represents the
number
of
hours i want to have separated from the text" difficulty is the fact
this
can
be displayed as 5.5hr, or 5.5 uur. secondly this doesn't necessary is 3
positions. could also be 5 uur. the only common part is that the data
is
ALWAYS at the right hand site of the text string

Try something like this:

Using Col_B as a "helper column":
B1:
=LEFT(A1,LEN(A1)-MAX(INDEX(COUNTIF(A1,"*"&$F$2:$F$13)*$G$2:$G$13,0) )+1)

Copy B1 down through Bxxx

This formula pulls the numbers from the end of those values:
C1: =IF(ISNUMBER(--MID(B1,MAX(FIND({"~","("},SUBSTITUTE(B1,"
","~",LEN(B1)-LEN(SUBSTITUTE(B1,"
","")))))+1,255)),--MID(B1,MAX(FIND({"~","("},SUBSTITUTE(B1,"
","~",LEN(B1)-LEN(SUBSTITUTE(B1," ","")))))+1,255),"na")

Copy C1 down through Cxxx

I am newly returned to Excel, so I am very rusty still... however, Jan said
he has 30,000 rows of data... wouldn't using a VBA macro be better than
loading up all those formulas into the spreadsheet directly?

Rick

ron, all results prints "na". is it correct the data in column B is equal to
column A?

"Ron Coderre" wrote:

With A1:A9 containing these values:

Site: 3480 test data migration
Home: Porterbrook / Ravers investigation (2 uur)
Home: installation on Cognos (5.5 hrs)
HOME upgrade laptop to pathc 08 (4hr)
voorbereiden upload sheets training (is uiteindelijk niet doorgegaan)
Helvoet aanmaken trainingsomgeving (CDE) 2 hr
netwerk problemen ( 3 hr)
WBGR - installation support (multi org)using email; 1 uur
new years celebration (Lunch) 3 hrs

Try something like this:

Using Col_B as a "helper column":
B1: =LEFT(A1,LEN(A1)-MAX(INDEX(COUNTIF(A1,"*"&$F$2:$F$13)*$G$2:$G$13,0) )+1)

Copy B1 down through B9

This formula pulls the numbers from the end of those values:
C1: =IF(ISNUMBER(--MID(B1,MAX(FIND({"~","("},SUBSTITUTE(B1,"
","~",LEN(B1)-LEN(SUBSTITUTE(B1,"
","")))))+1,255)),--MID(B1,MAX(FIND({"~","("},SUBSTITUTE(B1,"
","~",LEN(B1)-LEN(SUBSTITUTE(B1," ","")))))+1,255),"na")

Copy C1 down through C9
(Note: text wrap may adversely impact the display)

Using those formulas, C1:C9 returns these values:

na
2
5.5
4
na
na
3
1
3

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Jan" wrote:

biff, based on the formula i found my original description is incorrect,
sorry but i overlooked them cause of the number of records. on top of the
earlier given examples below the other exeptions.


Example d) HOME upgrade laptop to pathc 08 (4hr)
Example e) voorbereiden upload sheets training (is uiteindelijk niet
doorgegaan)
Example f) Helvoet aanmaken trainingsomgeving (CDE) 2 hr
Example g) netwerk problemen ( 3 hr)
Example h) WBGR - installation support (multi org)using email; 1 uur
Example i) new years celebration (Lunch) 3 hrs

Example j) 1) Nutricia Zoetermeer (migration to 8.2)

Example k) 2) Siemens (prospect) RFP (request Bert)
Example l) EMW shipment PRORDF modifications (DHA onsite) (1 uur)

Result d) should print 4
Result e) should print 0
Result f) should print 2
Result g) should print 3
Result h) should print 1
Result i) should print 3
Result j) should print 0
Result k) should print 0
Result l) should print 1

thanks.

"T. Valko" wrote:

Post *several representative samples* and tell us what the results for each
sample should be.

Biff

"Jan" wrote in message
...
thanks, the result indicates i need to narrow my question. another
conditon
would be the number to be represented must meet the condition captured
between brackets as it now also present general numbers.

"T. Valko" wrote:

Try this:

=LOOKUP(10^10,--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"01234567 89")),ROW(INDIRECT("1:255"))))

If the cell is either empty or doesn't have a number in the string the
formula will return #N/A. Also, it will extract the *first* number it
finds:

xxx 10.5 yy zzz 5.5

The result would be 10.5

Biff

"Jan" wrote in message
...
i have the following text string
"Home: installation on Cognos (5.5 hrs)" the number represents the
number
of
hours i want to have separated from the text" difficulty is the fact
this
can
be displayed as 5.5hr, or 5.5 uur. secondly this doesn't necessary is 3
positions. could also be 5 uur. the only common part is that the data
is
ALWAYS at the right hand site of the text string

i assume you meant F2:G13? i see in column B the changes, however column c
prints "na" for all rows.

"Ron Coderre" wrote:

OOPS! I forgot to post a significant part.....

Cells E2:F13 contain these values:

hr 5
hr 4
hr) 4
hr) 5
hrs 4
hrs 5
hrs) 5
hrs) 6
uur 4
uur 5
uur) 5
uur) 6

Note that some of the text begins with a space.

***********
Regards,
Ron

XL2002, WinXP


"Jan" wrote:

biff, based on the formula i found my original description is incorrect,
sorry but i overlooked them cause of the number of records. on top of the
earlier given examples below the other exeptions.


Example d) HOME upgrade laptop to pathc 08 (4hr)
Example e) voorbereiden upload sheets training (is uiteindelijk niet
doorgegaan)
Example f) Helvoet aanmaken trainingsomgeving (CDE) 2 hr
Example g) netwerk problemen ( 3 hr)
Example h) WBGR - installation support (multi org)using email; 1 uur
Example i) new years celebration (Lunch) 3 hrs

Example j) 1) Nutricia Zoetermeer (migration to 8.2)

Example k) 2) Siemens (prospect) RFP (request Bert)
Example l) EMW shipment PRORDF modifications (DHA onsite) (1 uur)

Result d) should print 4
Result e) should print 0
Result f) should print 2
Result g) should print 3
Result h) should print 1
Result i) should print 3
Result j) should print 0
Result k) should print 0
Result l) should print 1

thanks.

"T. Valko" wrote:

Post *several representative samples* and tell us what the results for each
sample should be.

Biff

"Jan" wrote in message
...
thanks, the result indicates i need to narrow my question. another
conditon
would be the number to be represented must meet the condition captured
between brackets as it now also present general numbers.

"T. Valko" wrote:

Try this:

=LOOKUP(10^10,--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"01234567 89")),ROW(INDIRECT("1:255"))))

If the cell is either empty or doesn't have a number in the string the
formula will return #N/A. Also, it will extract the *first* number it
finds:

xxx 10.5 yy zzz 5.5

The result would be 10.5

Biff

"Jan" wrote in message
...
i have the following text string
"Home: installation on Cognos (5.5 hrs)" the number represents the
number
of
hours i want to have separated from the text" difficulty is the fact
this
can
be displayed as 5.5hr, or 5.5 uur. secondly this doesn't necessary is 3
positions. could also be 5 uur. the only common part is that the data
is
ALWAYS at the right hand site of the text string

The Col_B values should match the Col_A values LESS the matched pattern on
the right side.

Example:
A2: Home: Porterbrook / Ravers investigation (2 uur)
B2: Home: Porterbrook / Ravers investigation (2

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Jan" wrote:

ron, all results prints "na". is it correct the data in column B is equal to
column A?

"Ron Coderre" wrote:

With A1:A9 containing these values:

Site: 3480 test data migration
Home: Porterbrook / Ravers investigation (2 uur)
Home: installation on Cognos (5.5 hrs)
HOME upgrade laptop to pathc 08 (4hr)
voorbereiden upload sheets training (is uiteindelijk niet doorgegaan)
Helvoet aanmaken trainingsomgeving (CDE) 2 hr
netwerk problemen ( 3 hr)
WBGR - installation support (multi org)using email; 1 uur
new years celebration (Lunch) 3 hrs

Try something like this:

Using Col_B as a "helper column":
B1: =LEFT(A1,LEN(A1)-MAX(INDEX(COUNTIF(A1,"*"&$F$2:$F$13)*$G$2:$G$13,0) )+1)

Copy B1 down through B9

This formula pulls the numbers from the end of those values:
C1: =IF(ISNUMBER(--MID(B1,MAX(FIND({"~","("},SUBSTITUTE(B1,"
","~",LEN(B1)-LEN(SUBSTITUTE(B1,"
","")))))+1,255)),--MID(B1,MAX(FIND({"~","("},SUBSTITUTE(B1,"
","~",LEN(B1)-LEN(SUBSTITUTE(B1," ","")))))+1,255),"na")

Copy C1 down through C9
(Note: text wrap may adversely impact the display)

Using those formulas, C1:C9 returns these values:

na
2
5.5
4
na
na
3
1
3

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Jan" wrote:

biff, based on the formula i found my original description is incorrect,
sorry but i overlooked them cause of the number of records. on top of the
earlier given examples below the other exeptions.


Example d) HOME upgrade laptop to pathc 08 (4hr)
Example e) voorbereiden upload sheets training (is uiteindelijk niet
doorgegaan)
Example f) Helvoet aanmaken trainingsomgeving (CDE) 2 hr
Example g) netwerk problemen ( 3 hr)
Example h) WBGR - installation support (multi org)using email; 1 uur
Example i) new years celebration (Lunch) 3 hrs

Example j) 1) Nutricia Zoetermeer (migration to 8.2)

Example k) 2) Siemens (prospect) RFP (request Bert)
Example l) EMW shipment PRORDF modifications (DHA onsite) (1 uur)

Result d) should print 4
Result e) should print 0
Result f) should print 2
Result g) should print 3
Result h) should print 1
Result i) should print 3
Result j) should print 0
Result k) should print 0
Result l) should print 1

thanks.

"T. Valko" wrote:

Post *several representative samples* and tell us what the results for each
sample should be.

Biff

"Jan" wrote in message
...
thanks, the result indicates i need to narrow my question. another
conditon
would be the number to be represented must meet the condition captured
between brackets as it now also present general numbers.

"T. Valko" wrote:

Try this:

=LOOKUP(10^10,--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"01234567 89")),ROW(INDIRECT("1:255"))))

If the cell is either empty or doesn't have a number in the string the
formula will return #N/A. Also, it will extract the *first* number it
finds:

xxx 10.5 yy zzz 5.5

The result would be 10.5

Biff

"Jan" wrote in message
...
i have the following text string
"Home: installation on Cognos (5.5 hrs)" the number represents the
number
of
hours i want to have separated from the text" difficulty is the fact
this
can
be displayed as 5.5hr, or 5.5 uur. secondly this doesn't necessary is 3
positions. could also be 5 uur. the only common part is that the data
is
ALWAYS at the right hand site of the text string

ron, it works :-) copy past from the forum to Excel caused an additional
character.
by using e:f referencing the different abbreviations and the number of
characters to delete i should be able to retreive my data.

thanks.

"Ron Coderre" wrote:

The Col_B values should match the Col_A values LESS the matched pattern on
the right side.

Example:
A2: Home: Porterbrook / Ravers investigation (2 uur)
B2: Home: Porterbrook / Ravers investigation (2

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Jan" wrote:

ron, all results prints "na". is it correct the data in column B is equal to
column A?

"Ron Coderre" wrote:

With A1:A9 containing these values:

Site: 3480 test data migration
Home: Porterbrook / Ravers investigation (2 uur)
Home: installation on Cognos (5.5 hrs)
HOME upgrade laptop to pathc 08 (4hr)
voorbereiden upload sheets training (is uiteindelijk niet doorgegaan)
Helvoet aanmaken trainingsomgeving (CDE) 2 hr
netwerk problemen ( 3 hr)
WBGR - installation support (multi org)using email; 1 uur
new years celebration (Lunch) 3 hrs

Try something like this:

Using Col_B as a "helper column":
B1: =LEFT(A1,LEN(A1)-MAX(INDEX(COUNTIF(A1,"*"&$F$2:$F$13)*$G$2:$G$13,0) )+1)

Copy B1 down through B9

This formula pulls the numbers from the end of those values:
C1: =IF(ISNUMBER(--MID(B1,MAX(FIND({"~","("},SUBSTITUTE(B1,"
","~",LEN(B1)-LEN(SUBSTITUTE(B1,"
","")))))+1,255)),--MID(B1,MAX(FIND({"~","("},SUBSTITUTE(B1,"
","~",LEN(B1)-LEN(SUBSTITUTE(B1," ","")))))+1,255),"na")

Copy C1 down through C9
(Note: text wrap may adversely impact the display)

Using those formulas, C1:C9 returns these values:

na
2
5.5
4
na
na
3
1
3

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Jan" wrote:

biff, based on the formula i found my original description is incorrect,
sorry but i overlooked them cause of the number of records. on top of the
earlier given examples below the other exeptions.


Example d) HOME upgrade laptop to pathc 08 (4hr)
Example e) voorbereiden upload sheets training (is uiteindelijk niet
doorgegaan)
Example f) Helvoet aanmaken trainingsomgeving (CDE) 2 hr
Example g) netwerk problemen ( 3 hr)
Example h) WBGR - installation support (multi org)using email; 1 uur
Example i) new years celebration (Lunch) 3 hrs

Example j) 1) Nutricia Zoetermeer (migration to 8.2)

Example k) 2) Siemens (prospect) RFP (request Bert)
Example l) EMW shipment PRORDF modifications (DHA onsite) (1 uur)

Result d) should print 4
Result e) should print 0
Result f) should print 2
Result g) should print 3
Result h) should print 1
Result i) should print 3
Result j) should print 0
Result k) should print 0
Result l) should print 1

thanks.

"T. Valko" wrote:

Post *several representative samples* and tell us what the results for each
sample should be.

Biff

"Jan" wrote in message
...
thanks, the result indicates i need to narrow my question. another
conditon
would be the number to be represented must meet the condition captured
between brackets as it now also present general numbers.

"T. Valko" wrote:

Try this:

=LOOKUP(10^10,--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"01234567 89")),ROW(INDIRECT("1:255"))))

If the cell is either empty or doesn't have a number in the string the
formula will return #N/A. Also, it will extract the *first* number it
finds:

xxx 10.5 yy zzz 5.5

The result would be 10.5

Biff

"Jan" wrote in message
...
i have the following text string
"Home: installation on Cognos (5.5 hrs)" the number represents the
number
of
hours i want to have separated from the text" difficulty is the fact
this
can
be displayed as 5.5hr, or 5.5 uur. secondly this doesn't necessary is 3
positions. could also be 5 uur. the only common part is that the data
is
ALWAYS at the right hand site of the text string

Hi, Rick

Since my posted formulas don't use an array formulas or incrementally
expanding formulas (eg COUNTIF($A$1:$A2,"whatever") ), the performance hit
is minimal.

I tested the formulas in a 30,000 row range....then sorted the first column
ascending/descending. The recalcs each took less than 2 seconds.

A UDF might look more elegant, but they are usually relatively sluggish.
I'm not sure it could match that same performance level. Consequently, it may
not be worth having the annoying Macro Warning if that's the only code in the
workbook.

***********
Regards,
Ron

XL2002, WinXP


"Rick Rothstein (MVP - VB)" wrote:

Try something like this:

Using Col_B as a "helper column":
B1:
=LEFT(A1,LEN(A1)-MAX(INDEX(COUNTIF(A1,"*"&$F$2:$F$13)*$G$2:$G$13,0) )+1)

Copy B1 down through Bxxx

This formula pulls the numbers from the end of those values:
C1: =IF(ISNUMBER(--MID(B1,MAX(FIND({"~","("},SUBSTITUTE(B1,"
","~",LEN(B1)-LEN(SUBSTITUTE(B1,"
","")))))+1,255)),--MID(B1,MAX(FIND({"~","("},SUBSTITUTE(B1,"
","~",LEN(B1)-LEN(SUBSTITUTE(B1," ","")))))+1,255),"na")

Copy C1 down through Cxxx

I am newly returned to Excel, so I am very rusty still... however, Jan said
he has 30,000 rows of data... wouldn't using a VBA macro be better than
loading up all those formulas into the spreadsheet directly?

Rick

I'm glad that helped.......Thanks for letting me know.

***********
Regards,
Ron

XL2002, WinXP


"Jan" wrote:

ron, it works :-) copy past from the forum to Excel caused an additional
character.
by using e:f referencing the different abbreviations and the number of
characters to delete i should be able to retreive my data.

thanks.

"Ron Coderre" wrote:

The Col_B values should match the Col_A values LESS the matched pattern on
the right side.

Example:
A2: Home: Porterbrook / Ravers investigation (2 uur)
B2: Home: Porterbrook / Ravers investigation (2

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Jan" wrote:

ron, all results prints "na". is it correct the data in column B is equal to
column A?

"Ron Coderre" wrote:

With A1:A9 containing these values:

Site: 3480 test data migration
Home: Porterbrook / Ravers investigation (2 uur)
Home: installation on Cognos (5.5 hrs)
HOME upgrade laptop to pathc 08 (4hr)
voorbereiden upload sheets training (is uiteindelijk niet doorgegaan)
Helvoet aanmaken trainingsomgeving (CDE) 2 hr
netwerk problemen ( 3 hr)
WBGR - installation support (multi org)using email; 1 uur
new years celebration (Lunch) 3 hrs

Try something like this:

Using Col_B as a "helper column":
B1: =LEFT(A1,LEN(A1)-MAX(INDEX(COUNTIF(A1,"*"&$F$2:$F$13)*$G$2:$G$13,0) )+1)

Copy B1 down through B9

This formula pulls the numbers from the end of those values:
C1: =IF(ISNUMBER(--MID(B1,MAX(FIND({"~","("},SUBSTITUTE(B1,"
","~",LEN(B1)-LEN(SUBSTITUTE(B1,"
","")))))+1,255)),--MID(B1,MAX(FIND({"~","("},SUBSTITUTE(B1,"
","~",LEN(B1)-LEN(SUBSTITUTE(B1," ","")))))+1,255),"na")

Copy C1 down through C9
(Note: text wrap may adversely impact the display)

Using those formulas, C1:C9 returns these values:

na
2
5.5
4
na
na
3
1
3

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Jan" wrote:

biff, based on the formula i found my original description is incorrect,
sorry but i overlooked them cause of the number of records. on top of the
earlier given examples below the other exeptions.


Example d) HOME upgrade laptop to pathc 08 (4hr)
Example e) voorbereiden upload sheets training (is uiteindelijk niet
doorgegaan)
Example f) Helvoet aanmaken trainingsomgeving (CDE) 2 hr
Example g) netwerk problemen ( 3 hr)
Example h) WBGR - installation support (multi org)using email; 1 uur
Example i) new years celebration (Lunch) 3 hrs

Example j) 1) Nutricia Zoetermeer (migration to 8.2)

Example k) 2) Siemens (prospect) RFP (request Bert)
Example l) EMW shipment PRORDF modifications (DHA onsite) (1 uur)

Result d) should print 4
Result e) should print 0
Result f) should print 2
Result g) should print 3
Result h) should print 1
Result i) should print 3
Result j) should print 0
Result k) should print 0
Result l) should print 1

thanks.

"T. Valko" wrote:

Post *several representative samples* and tell us what the results for each
sample should be.

Biff

"Jan" wrote in message
...
thanks, the result indicates i need to narrow my question. another
conditon
would be the number to be represented must meet the condition captured
between brackets as it now also present general numbers.

"T. Valko" wrote:

Try this:

=LOOKUP(10^10,--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"01234567 89")),ROW(INDIRECT("1:255"))))

If the cell is either empty or doesn't have a number in the string the
formula will return #N/A. Also, it will extract the *first* number it
finds:

xxx 10.5 yy zzz 5.5

The result would be 10.5

Biff

"Jan" wrote in message
...
i have the following text string
"Home: installation on Cognos (5.5 hrs)" the number represents the
number
of
hours i want to have separated from the text" difficulty is the fact
this
can
be displayed as 5.5hr, or 5.5 uur. secondly this doesn't necessary is 3
positions. could also be 5 uur. the only common part is that the data
is
ALWAYS at the right hand site of the text string

A UDF might look more elegant, but they are usually relatively sluggish.
I'm not sure it could match that same performance level.

Ah, I see what you mean. The UDF I developed (maybe it could be made a
little more efficient, however it) took 19 seconds to process a 30,000 row
sample I created (as opposed to your under 2 second solution).

Rick

Ron,

could you validate the next
- the text string is "Amey - intern support development environment; 1 uur"
- the result after applying your solution is "Amey - intern support
development environment; 1" however the result gives an error. i verified the
text string with similair lines but cannot find the solution. i also used the
option "evaluate formula" which shows we have a value error. again i cannot
find the difference. Please assist.



"Ron Coderre" wrote:

I'm glad that helped.......Thanks for letting me know.

***********
Regards,
Ron

XL2002, WinXP


"Jan" wrote:

ron, it works :-) copy past from the forum to Excel caused an additional
character.
by using e:f referencing the different abbreviations and the number of
characters to delete i should be able to retreive my data.

thanks.

"Ron Coderre" wrote:

The Col_B values should match the Col_A values LESS the matched pattern on
the right side.

Example:
A2: Home: Porterbrook / Ravers investigation (2 uur)
B2: Home: Porterbrook / Ravers investigation (2

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Jan" wrote:

ron, all results prints "na". is it correct the data in column B is equal to
column A?

"Ron Coderre" wrote:

With A1:A9 containing these values:

Site: 3480 test data migration
Home: Porterbrook / Ravers investigation (2 uur)
Home: installation on Cognos (5.5 hrs)
HOME upgrade laptop to pathc 08 (4hr)
voorbereiden upload sheets training (is uiteindelijk niet doorgegaan)
Helvoet aanmaken trainingsomgeving (CDE) 2 hr
netwerk problemen ( 3 hr)
WBGR - installation support (multi org)using email; 1 uur
new years celebration (Lunch) 3 hrs

Try something like this:

Using Col_B as a "helper column":
B1: =LEFT(A1,LEN(A1)-MAX(INDEX(COUNTIF(A1,"*"&$F$2:$F$13)*$G$2:$G$13,0) )+1)

Copy B1 down through B9

This formula pulls the numbers from the end of those values:
C1: =IF(ISNUMBER(--MID(B1,MAX(FIND({"~","("},SUBSTITUTE(B1,"
","~",LEN(B1)-LEN(SUBSTITUTE(B1,"
","")))))+1,255)),--MID(B1,MAX(FIND({"~","("},SUBSTITUTE(B1,"
","~",LEN(B1)-LEN(SUBSTITUTE(B1," ","")))))+1,255),"na")

Copy C1 down through C9
(Note: text wrap may adversely impact the display)

Using those formulas, C1:C9 returns these values:

na
2
5.5
4
na
na
3
1
3

Does that help?
***********
Regards,
Ron

XL2002, WinXP


"Jan" wrote:

biff, based on the formula i found my original description is incorrect,
sorry but i overlooked them cause of the number of records. on top of the
earlier given examples below the other exeptions.


Example d) HOME upgrade laptop to pathc 08 (4hr)
Example e) voorbereiden upload sheets training (is uiteindelijk niet
doorgegaan)
Example f) Helvoet aanmaken trainingsomgeving (CDE) 2 hr
Example g) netwerk problemen ( 3 hr)
Example h) WBGR - installation support (multi org)using email; 1 uur
Example i) new years celebration (Lunch) 3 hrs

Example j) 1) Nutricia Zoetermeer (migration to 8.2)

Example k) 2) Siemens (prospect) RFP (request Bert)
Example l) EMW shipment PRORDF modifications (DHA onsite) (1 uur)

Result d) should print 4
Result e) should print 0
Result f) should print 2
Result g) should print 3
Result h) should print 1
Result i) should print 3
Result j) should print 0
Result k) should print 0
Result l) should print 1

thanks.

"T. Valko" wrote:

Post *several representative samples* and tell us what the results for each
sample should be.

Biff

"Jan" wrote in message
...
thanks, the result indicates i need to narrow my question. another
conditon
would be the number to be represented must meet the condition captured
between brackets as it now also present general numbers.

"T. Valko" wrote:

Try this:

=LOOKUP(10^10,--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"01234567 89")),ROW(INDIRECT("1:255"))))

If the cell is either empty or doesn't have a number in the string the
formula will return #N/A. Also, it will extract the *first* number it
finds:

xxx 10.5 yy zzz 5.5

The result would be 10.5

Biff

"Jan" wrote in message
...
i have the following text string
"Home: installation on Cognos (5.5 hrs)" the number represents the
number
of
hours i want to have separated from the text" difficulty is the fact
this
can
be displayed as 5.5hr, or 5.5 uur. secondly this doesn't necessary is 3
positions. could also be 5 uur. the only common part is that the data
is
ALWAYS at the right hand site of the text string

This bit of VBA should sort you out.

As I don't know where your data resides on your worksheet :
1. Copy your data and paste in cell A1 of a new workbook or worksheet
2. Copy the code below and paste in the VBE
3. Run the macro "GetHours"
4. Your hours should now be in Col B

---------------------------------------------------
Sub GetHours()

Dim r As Integer
Application.ScreenUpdating = False
Application.DisplayAlerts = False

With Columns("A")
..SpecialCells(xlCellTypeBlanks).EntireRow.Delete
..Replace What:=" hr", Replacement:="hr", LookAt:=xlPart
..Replace What:=" uur", Replacement:="hr", LookAt:=xlPart
..Replace What:=" hrs", Replacement:="hr", LookAt:=xlPart
..Replace What:="hrs", Replacement:="hr", LookAt:=xlPart
End With

r = Range("A1").CurrentRegion.Rows.Count - 1

With Range(Range("B1"), Range("B1").Offset(r, 0))
..FormulaR1C1 = _
"=IF(ISERR(FIND(""hr"",RC[-1])),0,MID(RC[-1],FIND(""hr"",RC[-1])-1,1))"
..Select
End With

Selection.Value = Selection.Value
Range("A1").Select

Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
-------------------------------------------------
Regards

Dave Gibson
Leeds U.K.


EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com

The code I posted above is not showing correctly.
As there is no option here to correct the post I am attaching the correct code below.

---------------------------------------------------

Sub GetHours()

Dim r As Integer
Application.ScreenUpdating = False
Application.DisplayAlerts = False

With Columns("A")
..SpecialCells(xlCellTypeBlanks).EntireRow.Delete
..Replace What:=" hr", Replacement:="hr", LookAt:=xlPart
..Replace What:=" uur", Replacement:="hr", LookAt:=xlPart
..Replace What:=" hrs", Replacement:="hr", LookAt:=xlPart
..Replace What:="hrs", Replacement:="hr", LookAt:=xlPart
End With

r = Range("A1").CurrentRegion.Rows.Count

With Range(Range("B1"), Range("B1").Offset(r, 0))
..FormulaR1C1 = _
"=IF(ISERR(FIND(""hr"",RC[-1])),0,MID(RC[-1],FIND(""hr"",RC[-1])-1,1))"
..Select
End With

Selection.Value = Selection.Value
Range("A1").Select

Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub

EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com

The code is still not showing corrrectly!
The formula shown in the code after FormulaR1C1 = _
should be :

=IF(ISERR(FIND(""hr"",RC[-1])),0,MID(RC[-1],FIND(""hr"",RC[-1])-1,1))

surounded by double quotes "......"

EggHeadCafe.com - .NET Developer Portal of Choice
http://www.eggheadcafe.com