+ Reply to Thread
Results 1 to 5 of 5

How to delete all the characters in cell and leave just the numbers?

  1. #1
    Forum Contributor
    Join Date
    06-09-2008
    Posts
    124

    How to delete all the characters in cell and leave just the numbers?

    For example, cell "A1=d 123-c\ 54.348xD c541"

    The result I need is "B1=12354348541"

    Now I'm use with SUBSTITUTE function like B1=SUBSTITUTE(A1,"-","") but is not very good
    Thank you

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

    Return only the numeric chars from an alphanumeric cell

    With
    A1: d 123-c\ 54.348xD c541

    Try this:

    First, create a Named Formula....
    From the Excel Main Menu: <insert><name><define>
    Please Login or Register  to view this content.
    This ARRAY FORMULA (committed with CTRL+SHIFT+ENTER, instead of just ENTER) removes ALL non-numerics from a string
    Please Login or Register  to view this content.
    In the example, the formula returns: 12354348541

    NOTE: That formula can only return Excel's limit of 15 digits.

    (Credit goes to Harlan Grove for that solution)
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  3. #3
    Forum Contributor
    Join Date
    06-09-2008
    Posts
    124

    Thank you but is complicated and limit to 15 digits

    How to combining in one cell more than one SUBSTITUTE function ? like
    B1=SUBSTITUTE(A1,"-","") and =SUBSTITUTE(A1,"(","") and =SUBSTITUTE(A1,".","")

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

    Nesting SUBSTITUTE functions

    Well, to combine the formulas in your example.....

    Try this:
    Please Login or Register  to view this content.
    Does that help?

  5. #5
    Forum Contributor
    Join Date
    06-09-2008
    Posts
    124

    Great !

    Thank you

+ 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