how do i separate numbers and text in a cell?

[Guest]

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

 

[T. Valko]

Try this:

=LOOKUP(10^10,--MID(A1,MIN(FIND({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),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

 

[Guest]

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]

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

Biff

 

[Dave Peterson]

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

 

[Guest]

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

 

[Gord Dibben]

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

 

[T. Valko]

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&"0123456789")),ROW(INDIRECT("1:255")))),"")

Biff

 

[Guest]

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]

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).

 

[T. Valko]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Guest]

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.

 

[Guest]

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

 

[Rick Rothstein \(MVP - VB\)]

example a) Site: 3480 test data migration

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

 

[Guest]

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

 

[Guest]

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?

 

[T. Valko]

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

 

[mikerickson]

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


Code:
--------------------
Function numberWithin(inputString As String) As Double
Dim rightHalt As Integer, choppedStr As String, i As Long, flag As Boolean
rightHalt = InStr(inputString, "hr")
If rightHalt = 0 Then rightHalt = InStr(inputString, "uur")
choppedStr = Left(inputString, rightHalt)
For i = 1 To Len(choppedStr)
If flag Then
If Val(Right(choppedStr, i)) = 0 Then Exit Function
Else
If Val(Right(choppedStr, i)) <> 0 Then flag = True
End If
numberWithin = Val(Right(choppedStr, i))
Next i
End Function

 

[Guest]

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.