Search for text, copy the number in front of text

I have 3 columns of data and am doing a number of things on it. Most of the stuff I figured out from the recorder and just taking formulas and integrating those into a macro.
The problem is copying a numeric value from the cells to another cell. Some of the cells contain the text "mil" and are preceeded by a number. I was wondering how it would be coded in the macro to search through the cells for the text "mil" then take the value before it and copy it to another cell.
I know how to place the value once you have it into another cell. I just don't know how to get the value.

Help/Links are helpful. I tried searching but don't know if I was using correct search text.

TIA

This line returns the value include in a text string

say that A1 = 25 mils
x = 25

dim x as Double

x = Val(Range("A1")

--
steveB

Remove "AYN" from email to respond
"w0cyru01" <[email protected]> wrote in
message news:[email protected]...
>
> I have 3 columns of data and am doing a number of things on it. Most of
> the stuff I figured out from the recorder and just taking formulas and
> integrating those into a macro.
> The problem is copying a numeric value from the cells to another cell.
> Some of the cells contain the text "mil" and are preceeded by a number.
> I was wondering how it would be coded in the macro to search through the
> cells for the text "mil" then take the value before it and copy it to
> another cell.
> I know how to place the value once you have it into another cell. I
> just don't know how to get the value.
>
> Help/Links are helpful. I tried searching but don't know if I was
> using correct search text.
>
> TIA
>
>
> --
> w0cyru01
> ------------------------------------------------------------------------
> w0cyru01's Profile:
> http://www.excelforum.com/member.php...o&userid=25817
> View this thread: http://www.excelforum.com/showthread...hreadid=392158
>

I wish it were that simple. The cell contains more than just "# mil". Sometimes it can be "lead 1 2.34 mil" or "**>1 3.42 mil" "2.12 mil *>1" or other variations. If I use val it just returns 0. Is there a find command that finds the mil and is able to copy the 4 characters before it?

Check out the InString function (InStr)

You can use that to find the position of "mil" and than
combine it in a mixture of Val(Mid(....))

But if mil is always "mil"

Than you take the cel and remove the last 3 characters and than look at the
last 4 characters.

x = Val(Right(Left(cel,len(cel)-3),4))

--
steveB

Remove "AYN" from email to respond
"w0cyru01" <[email protected]> wrote in
message news:[email protected]...
>
> I wish it were that simple. The cell contains more than just "# mil".
> Sometimes it can be "lead 1 2.34 mil" or "**>1 3.42 mil" "2.12 mil *>1"
> or other variations. If I use val it just returns 0. Is there a find
> command that finds the mil and is able to copy the 4 characters before
> it?
>
>
> --
> w0cyru01
> ------------------------------------------------------------------------
> w0cyru01's Profile:
> http://www.excelforum.com/member.php...o&userid=25817
> View this thread: http://www.excelforum.com/showthread...hreadid=392158
>

Thank you the InStr command was able to allow me to do what I needed to do.