+ Reply to Thread
Results 1 to 5 of 5

Search for text, copy the number in front of text

  1. #1
    Registered User
    Join Date
    08-02-2005
    Posts
    3

    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

  2. #2
    STEVE BELL
    Guest

    Re: 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" <w0cyru01.1t56yl_1122995158.4926@excelforum-nospam.com> wrote in
    message news:w0cyru01.1t56yl_1122995158.4926@excelforum-nospam.com...
    >
    > 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. #3
    Registered User
    Join Date
    08-02-2005
    Posts
    3
    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?

  4. #4
    STEVE BELL
    Guest

    Re: 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" <w0cyru01.1t5i2p_1123009566.4885@excelforum-nospam.com> wrote in
    message news:w0cyru01.1t5i2p_1123009566.4885@excelforum-nospam.com...
    >
    > 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. #5
    Registered User
    Join Date
    08-02-2005
    Posts
    3
    Thank you the InStr command was able to allow me to do what I needed to do.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1