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 o
the stuff I figured out from the recorder and just taking formulas an
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 th
cells for the text "mil" then take the value before it and copy it t
another cell.
I know how to place the value once you have it into another cell.
just don't know how to get the value.

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

TI

--
w0cyru0
-----------------------------------------------------------------------
w0cyru01's Profile: http://www.excelforum.com/member.php...fo&userid=2581
View this thread: http://www.excelforum.com/showthread.php?threadid=39215

Search for text, copy the number in front of text
 

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" wrote in
message ...

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

Search for text, copy the number in front of text
 

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 fin
command that finds the mil and is able to copy the 4 characters befor
it

--
w0cyru0
-----------------------------------------------------------------------
w0cyru01's Profile: http://www.excelforum.com/member.php...fo&userid=2581
View this thread: http://www.excelforum.com/showthread.php?threadid=39215

Search for text, copy the number in front of text
 

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" wrote in
message ...

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

Search for text, copy the number in front of text
 

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


--
w0cyru01
------------------------------------------------------------------------
w0cyru01's Profile: http://www.excelforum.com/member.php...o&userid=25817
View this thread: http://www.excelforum.com/showthread...hreadid=392158