+ Reply to Thread
Results 1 to 9 of 9

Capture Only Numbers in a string

  1. #1
    Registered User
    Join Date
    11-18-2008
    Location
    Dallas
    Posts
    67

    Capture Only Numbers in a string

    Is there a function in Excel that will allow me to only capture the numbers in a string? Below is a list and I would like to in a seperate column only capture the numbers in each of the below strings. I can't seem to figure out how to get rid of the *



    HB 8185******
    HB 8214******
    HB 8216
    HB 8217******
    HB 8220******

  2. #2
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320
    use find replace
    prefix the * with tilde ~
    so find
    ~*
    replace with nothing

  3. #3
    Registered User
    Join Date
    11-18-2008
    Location
    Dallas
    Posts
    67

    Re:Capture Only Numbers in a string

    There maybe times when I have hundreds or even thousands of lines of data how can I add the prefix ~ between the number and * easily?


    Thanks

  4. #4
    Forum Expert
    Join Date
    12-23-2006
    Location
    germany
    MS-Off Ver
    XL2003 / 2007 / 2010
    Posts
    6,326
    If the format is always the same try Data - Text to columns - Fixed width insert delimiters OK

  5. #5
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492
    I think you may need to add in a User Defined Function to do this simply. This page explains how to do it fully:
    http://www.ozgrid.com/VBA/ExtractNum.htm

    Once you've installed the User Defined Function ExtractNumber, then this formula would be all you need if the data were in column A starting at A1:
    Please Login or Register  to view this content.
    Remember, this won't work until YOU install this new function following the instructions on that link.

    Good luck.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  6. #6
    Forum Guru
    Join Date
    08-26-2007
    Location
    London
    Posts
    4,607
    Another approach in case you're bored:
    Please Login or Register  to view this content.

  7. #7
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320
    i'm confused!!! select column(s) or whole work sheet with your data in
    and use find /replace as i said . that will delete all * from your data
    or were you using * as an example of stuff you didnt want?
    of course you can use text to columns to seperate ***** from rest but what is the point?

  8. #8
    Cheeky Forum Moderator Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Capture Only Numbers in a string

    With
    A1 containing a string with consecutive numbers....eg HB 8185******


    This formula pulls only the consecutive numbers from that cell
    Please Login or Register  to view this content.
    In the above example, the formula returns: 8185

    Is that something you can work with?
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  9. #9
    Forum Guru martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320
    i am unconfused!
    you dont have to put ~ in front of the * on your worksheet.
    you only use it in the find screen the ~ makes the find look for *
    if it was ommited and you just did find * /replace nothing it would treat the *as a wild card and delete everything
    just do as in attached image and hit replace all
    Attached Images Attached Images
    Last edited by martindwilson; 11-20-2008 at 07:40 AM.

+ 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