extract number from string 1

[mirage10]

Hi ,
In excel 2000 i have data in a column like
200mg
400 mcg
8000 IU

I have to perform calculations on the number part of the cell

some times i will have to divide this column by 4
then i want the data as
50mg
100mcg
2000IU.

is there a way we can separate the number from this string and perform calculations on them??

------------------------------------------
A Man can be only as happy.. as he makes up his mind to be

 

[SkipVought]

Hi,

Best to have the NUMBERS and UNITS in separate columns and then concatenate if necessary.

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[mirage10]

the data is entered by the customers in this format.. we cannot do any thing at their end.. we will have to figure out a way at our end..



------------------------------------------
A Man can be only as happy.. as he makes up his mind to be

 

[KenWright]

Was the first entry a typo, or do you have a mix of data where you have a space between some and not others?

Regards
Ken..........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[SkipVought]

The only way I'd know to do it is to parse each string in a VBA Procedure. You'd need a function to return either the number or the units...

Function NbrUnits(rng As Range, typ As Integer)
   Dim r As Range, b As String, i As Integer
   For i = 1 To Len(rng.Value)
       b = Mid(rng.Value, i, 1)
       Select Case b
          Case "0" To "9"
             If typ = 1 Then _
               NbrUnits = NbrUnits & b
          Case Else
            If typ = 2 Then _
               NbrUnits = Trim(NbrUnits & b)
       End Select
   Next

End Function

typ = 1 for number and 2 for units
paste into a MODULE
use like any other function -- listed in USER DEFINED FUNCTIONS

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[KenWright]

Assuming your text is in say A1, and that you wanted to divide the value by 4, then the following will return the number part of your text, do whatever you change the /4 to, and then append the text from the original value back on to it:-

=(--LEFT(A1,MIN(MATCH(FALSE(),ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0))-1)/4)&MID(A1,MIN(MATCH(FALSE(),ISNUMBER(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)),0)),LEN(A1))

Array entered using CTRL+SHIFT+ENTER

Regards
Ken...............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[macropod]

If the cell format is the same for all entries and the number is always followed by a space (i.e. Number<space>Text), then:
=VALUE(LEFT(A1,FIND(" ",A1)-1)))
will return the numeric portion of the string.

However, if the cell format is not the same for all entries or the number is not followed by a space, then:
=--LEFT(A13,LEN(A1)-MIN(IF(ISNUMBER((--MID(A13,ROW(INDIRECT("1:"&LEN(A13))),1))),(ROW(INDIRECT("1:"&LEN(A13)))),""))+1)
will return the numeric portion of the string.

You can then do your other math on it.

Cheers

 

[KenWright]

Oops - didn't see you'd gotten rid of the spaces - slight tweak to fix that:-

=(--LEFT(A1,MIN(MATCH(FALSE(),ISNUMBER(--MID(A1,ROW(INDIRECT("1:1000")),1)),0))-1)/4)&SUBSTITUTE((MID(A1,MIN(MATCH(FALSE(),ISNUMBER(--MID(A1,ROW(INDIRECT("1:1000")),1)),0)),LEN(A1)))," ","")

Regards
Ken..........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[macropod]

Oops for me too:

The second formula should have been -
=--LEFT(A1,LEN(A1)-MAX(IF(ISNUMBER((--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),(ROW(INDIRECT("1:"&LEN(A1)))),""))+1)
(Array entered)

Cheers