Re: how do i separate numbers and text in a cell?
- From: "Rick Rothstein \(MVP - VB\)" <rickNOSPAMnews@xxxxxxxxxxxxxxxxx>
- Date: Sat, 2 Jun 2007 15:14:54 -0400
> > 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
.
- References:
- Re: how do i separate numbers and text in a cell?
- From: Rick Rothstein \(MVP - VB\)
- Re: how do i separate numbers and text in a cell?
- From: Jan
- Re: how do i separate numbers and text in a cell?
- Prev by Date: Re: 1/8 inch grid lines
- Next by Date: Re: AutoFilter defect or my mistake?
- Previous by thread: Re: how do i separate numbers and text in a cell?
- Next by thread: Re: how do i separate numbers and text in a cell?
- Index(es):
Relevant Pages
|
