+ Reply to Thread
Results 1 to 5 of 5

Extracting numbers from a string in a cell without recurring to VBA code

  1. #1
    Registered User
    Join Date
    04-02-2012
    Location
    Guatemala, Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    31

    Extracting numbers from a string in a cell without recurring to VBA code

    Hi

    I know how to extract numbers from a string using VBA. I recently learned about array functions and it ocurred to me that extracting numbers from a string might be accomplished with the use of such functions. So far I've been able to extract the numbers comparing the UCase and LCase of the string using an array function but I've been forced to place the result en an equal number of cells as to the number of characters of the string. I'd like to be able to concatenate each of the numbers and have the function's result in a single cell. My attempt at doing this is as follows:


    =IF(EXACT(UCASE(MID(B5,{1\2\3\4\5\6\7\8\9\10},1)),LCASE(MID(B5,{1\2\3\4\5\6\7\8\9\10},1)))*CODE(MID(B5,{1\2\3\4\5\6\7\8\9\10},1))<>0,1,2)


    the {1\2\3\4\5\6\7\8\9\10} part means I'll evaluate strings made of up to 10 characters (Though I'd like it very much if this could be done in a not fixed fashion, but accordingly to the string's length)

    So far, when evaluating the formula, for the string: "string 125" I come up with a vector : IF ({0|0|0|0|0|0|32|49|50|53}<>0,1,2) the number zeros meaning no coincidence between the UCASE and LCASE (in other words letters), the number 32 a blank space, numbers 49,50 and 53 representing the ascii code for the numbers 1,2 and 5.

    THE PROBLEM IS THAT FROM THERE, I DON'T KNOW HOW TO CONCATENATE THE CHARACTERS OF THOSE NUMBERS INTO A RESULT STRING THAT CONTAINS THE BLANK SPACE AND THE NUMBERS SO THAT THE RESULT CAN BE CONVERTED INTO A NUMERIC VALUE USING THE VAL FUNCTION.



    Thanks for any insight.

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    15,536

    Re: Extracting numbers from a string in a cell without recurring to VBA code

    Hi Guidoo,

    Does this site answer your question?
    http://office.microsoft.com/en-us/ex...001154901.aspx
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,391

    Re: Extracting numbers from a string in a cell without recurring to VBA code

    try this....

    '=LOOKUP(99^99,--("0"&MID(A1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789")),ROW($1:$10000)))) Courtesy of:*Ron Coderre
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  4. #4
    Registered User
    Join Date
    04-02-2012
    Location
    Guatemala, Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    31

    Re: Extracting numbers from a string in a cell without recurring to VBA code

    Hi MarvinP!

    Thank you very much for your advice! it works!

  5. #5
    Registered User
    Join Date
    04-02-2012
    Location
    Guatemala, Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    31

    Re: Extracting numbers from a string in a cell without recurring to VBA code

    Hi FDibbins!

    Well I'll be damned!. Don't really understand what you did but it works exactly like I wanted it to work!


    Thank You Very Much!

+ 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