help needed

T

Todd7724

I have a worksheet that has text and numbers in the same cell. I want to
multiply the numbers in that cell to another number in another cell. Any
suggestions??
 
S

Sandy Mann

Assuming that there is only one set of numbers, or you want all the numbers
treated as a consecutive number, (ie with "A1B2c3" or "ABD123" in the cell
you want the number 123 extracted), and the numbers will always be
integers, try the Custom formula:

Function NumberIt(Here As Range, There As Range)

n = 1

For x = Len(Here) To 1 Step -1
If IsNumeric(Mid(Here, x, 1)) Then
m = m + CInt(Mid(Here, x, 1)) * n
n = n * 10
End If
Next x

NumberIt = m * There

End Function

Then enter the formula:
=NumberIt(cell reference with text, cell reference with number)

ie NumberIt(D3,E3)

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
[email protected] with @tiscali.co.uk
 
T

Todd7724

6.00
2 x IN

I am looking for a formula that will give me the answer of 12 without
changing the look of either cell.
What the sheet does is pull data into excel from another program. 1 column
has a time (6.00) and the other has number of people (2 x IN). I need to
multiply these together to get the amount of time total for the job task
(another column).
 
S

Sandy Mann

If the layout of the cell is always: number <posible space> "X" text then
with the number/text in G11 and the time in H11

=LEFT(G11,SEARCH("x",G11)-1) * H11

should do what you want.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
[email protected] with @tiscali.co.uk
 
N

nbmagalhaes

6.00
2 x IN

I am looking for a formula that will give me the answer of 12 without
changing the look of either cell.
What the sheet does is pull data into excel from another program. 1 column
has a time (6.00) and the other has number of people (2 x IN). I need to
multiply these together to get the amount of time total for the job task
(another column).






- Mostrar texto entre aspas -
Click to expand...

Example:
Text Value Formula
8 cores 80 =left(céll;1)

Of the Amazonian Forest (Manaus/Amazon/Brazil) for the world.
 
Top