Results 1 to 5 of 5

Thread: Extract a number from a long Cell
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Aug 2011
    Posts
    231
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Extract a number from a long Cell

    This is the data in my cell:


    ANUZ2B2KI2XTG;ANUZ2B2KI2XTG;23.93+0=23.93;Me;26.12;Ig:r#5:A20AY8YZ1X41HY;A20AY8YZ1X41HY;23.96+0=23.96;Me;26.12;Ig:r#5:>A2FATTUNY7ADJL;A2FATTUNY7ADJL;23.99+0=23.99;Me;26.12;Ig:r#5:


    I am trying to extract the first number which is “23.93” from the cell
    Last edited by shlomek12; Sep 20th, 2019 at 12:04 PM.

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    North Yorkshire, UK
    Posts
    263
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract a number from a long Cell

    A quick and dirty way of doing this (assuminng all your data has exactly the same layout/structure in each cell) would be to use text to columns.

    Specify semicolon AND + as the delimiters. Skip the first two columns and then delete all other columns to the right of the one that now contains the value you want 23.93

    If this doesn't work for all of your data, you'll need to provide more examples (specifically for any rows that don't work using the quick method above).

    [edited to change two steps of TTC to one step)
    Last edited by JugglerJAF; Sep 20th, 2019 at 12:17 PM.
    It's curtains for your Dr. Horrible ... lacy, gently wafting curtains.

  3. #3
    Board Regular BlakeSkate's Avatar
    Join Date
    Jan 2015
    Location
    Pernsylvoonia
    Posts
    461
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Extract a number from a long Cell

    in order for there to be a clean cut way to do this you need to provide some sort of pattern that your data has
    i.e

    if this number always appears after the second set of semicolons, etc
    -------------------------------------------------------------------------------
    I may not give the best VBA codes, but they sure are VBA codes.
    Help me help you by posting a snapshot of your data & your expected result
    Please use [ code][ /code] tags when posting VBA as well as proper indentation

  4. #4
    MrExcel MVP
    Join Date
    May 2009
    Posts
    16,465
    Post Thanks / Like
    Mentioned
    36 Post(s)
    Tagged
    8 Thread(s)

    Default Re: Extract a number from a long Cell

    Assuming the number you want always appears immediately after the 2nd semicolon, and immediately before the first +
    Try (change cell reference to suit):
    Code:
    =MID(SUBSTITUTE(A1,";","@",2),SEARCH("@",SUBSTITUTE(A1,";","@",2))+1,SEARCH("+",SUBSTITUTE(A1,";","@",2))-SEARCH("@",SUBSTITUTE(A1,";","@",2))-1)+0
    Joe

    When I was a young man I knew everything. Now that I'm older, I realize I know very little, and what I do know, I tend to forget!

  5. #5
    MrExcel MVP Rick Rothstein's Avatar
    Join Date
    Apr 2011
    Location
    New Jersey, USA
    Posts
    35,239
    Post Thanks / Like
    Mentioned
    92 Post(s)
    Tagged
    33 Thread(s)

    Default Re: Extract a number from a long Cell

    Quote Originally Posted by JoeMo View Post
    Assuming the number you want always appears immediately after the 2nd semicolon, and immediately before the first +
    Using your assumptions, this shorter formula should also work...

    =0+MID(LEFT(A1,FIND("+",A1)-1),FIND(";",A1,FIND(";",A1)+1)+1,99)
    Rick's "mini" blog... http://www.excelfox.com/forum/f22/
    .
    Want to post a small screen shot? See Part B here.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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