+ Reply to Thread
Results 1 to 7 of 7

Search for a numerical value in the string

  1. 11-27-2014, 12:33 PM #1
    simasemail
    simasemail is offline
    Registered User
    Join Date
    11-27-2014
    Location
    Hampshire
    MS-Off Ver
    2010 Business
    Posts
    1

    Search for a numerical value in the string

    Hello, I need i bit of your help with excel.

    I am trying to create simple excel spreadsheet that can calculate price for material. However i don't know, how to create a function which can identify numerical value the string.

    For example I have a string from CAD software like X: 14.31 Y: 78.86 Z: 295.42
    Numbers indicates dimensions in mm. It can be anything from 0 to infinity.

    I want to create a function which can take x, y and z values and plot them separately on C4,C5,C6.

    Does the excel has a function to do it?
    Value in the string.xlsx

    Thanks

    Simon
    Reply With QuoteRegister To Reply
  2. 11-27-2014, 01:07 PM #2
    Special-K
    Special-K is offline
    Forum Expert
    Join Date
    03-23-2004
    Location
    London, England
    MS-Off Ver
    Excel 2019
    Posts
    7,082

    Re: Search for a numerical value in the string

    in C4
    =MID(C1,4,FIND("Y:",C1)-4)
    in C5
    =MID(C1,FIND("Y:",C1)+3,FIND("Z:",C1)-(FIND("Y:",C1)+3))
    in C6
    =RIGHT(C1,LEN(C1)-FIND("Z:",C1)-2)
    Regards
    Special-K

    Ensure you describe your problem clearly, I have little time available to solve these problems and do not appreciate numerous changes to them.
    Reply With QuoteRegister To Reply
  3. 11-27-2014, 01:36 PM #3
    Russell Dawson
    Russell Dawson is offline
    Forum Contributor Russell Dawson's Avatar
    Join Date
    03-01-2012
    Location
    Yorkshire, England
    MS-Off Ver
    2007
    Posts
    608

    Re: Search for a numerical value in the string

    Hi,

    With respect, Im not sure Special K has it right as the result is not numeric.

    I've been toying with Text to columns and made a macro to do as requested. See attached.
    Attached Files Attached Files
    If I helped, please don't forget to add to my reputation. (click on the star below the post)

    If the problem is solved, please: Select Thread Tools (on top of your 1st post) -> Mark this thread as Solved.

    Failure is not falling down but refusing to get up.
    Reply With QuoteRegister To Reply
  4. 11-27-2014, 02:15 PM #4
    Indi_Ra
    Indi_Ra is offline
    Banned User!
    Join Date
    10-29-2012
    Location
    Europe
    MS-Off Ver
    2013, 2016
    Posts
    318

    Re: Search for a numerical value in the string

    Hi,

    With respect, Im not sure Special K has it right as the result is not numeric.
    @Russel
    Neither your code does not return values, but text.

    Special-K Formulas are OK but the numbers must be put "+0" at the end of formulas (without quotes).
    =MID(C1,4,FIND("Y:",C1)-4)+0
    Or
    =1*MID(C1,4,FIND("Y:",C1)-4)
    Last edited by Indi_Ra; 11-27-2014 at 02:18 PM.
    Reply With QuoteRegister To Reply
  5. 11-27-2014, 03:48 PM #5
    Russell Dawson
    Russell Dawson is offline
    Forum Contributor Russell Dawson's Avatar
    Join Date
    03-01-2012
    Location
    Yorkshire, England
    MS-Off Ver
    2007
    Posts
    608

    Re: Search for a numerical value in the string

    So I was right then.

    My solution works fine albeit not by formula. The calc works fine.
    Reply With QuoteRegister To Reply
  6. 11-27-2014, 04:01 PM #6
    newdoverman
    newdoverman is offline
    Forum Expert newdoverman's Avatar
    Join Date
    02-07-2013
    Location
    Port Dover, Ontario, Canada
    MS-Off Ver
    2010
    Posts
    10,330

    Re: Search for a numerical value in the string

    Is your real data just 1 row like you show or is it several rows of XYZ values? If that is the case, what does it really look like?
    <---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.
    Ron W
    Reply With QuoteRegister To Reply
  7. 11-27-2014, 05:26 PM #7
    AlKey
    AlKey is offline
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Search for a numerical value in the string

    You can accomplish this one formula

    In C4 and copy down

    =TRIM(MID(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C$1,"X: ",""),"Y: ",""),"Z: ","")," ",REPT(" ",255)),(ROWS($A$1:A1)-1)*255+1,255))
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Search for string across header row, then search for another string down found column
    By TucsonJack in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-14-2012, 02:09 PM
  2. Assign Numerical Value to a Text String
    By getblended in forum Excel General
    Replies: 3
    Last Post: 01-12-2012, 11:40 AM
  3. Converting string to numerical value
    By tdh885 in forum Excel General
    Replies: 2
    Last Post: 05-14-2007, 07:54 AM
  4. [SOLVED] How do I change a numerical value to a string value ?
    By Tony Guzzo in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 03-30-2006, 07:40 PM
  5. search a string withing a string : find / search hangs
    By itarnak in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-24-2005, 11:05 AM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1