Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I copy text with a plus sign at the front? | Excel Discussion (Misc queries) | |||
How do I add a zero in front of a text formatt number? | Excel Worksheet Functions | |||
How do I add a zero in front of a text formatt number of 5000 reco | Excel Worksheet Functions | |||
Extract phone number front block of text | Excel Discussion (Misc queries) | |||
Modifiying a text number ( 12345 ) to have exactly 6 digit with a 0 in front | Excel Programming |