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