Results 1 to 10 of 10

Thread: Extract Only Numbers & TEXT From Text String

  1. #1
    Senior Member
    Join Date
    Mar 2013
    Posts
    146
    Rep Power
    0

    Extract Only Numbers & TEXT From Text String

    I need to separate the numbers and the text of the existing text in column (A)
    I need this solution by formulas, and use (Office 2003)
    Attached Files Attached Files
    Last edited by mahmoud-lee; 10-30-2013 at 06:48 PM.

  2. #2
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  3. #3
    Senior Member
    Join Date
    Mar 2013
    Posts
    146
    Rep Power
    0
    But I also want to separate the text away from the numbers
    As it is located in the Sheet

  4. #4
    Senior Member
    Join Date
    Mar 2013
    Posts
    146
    Rep Power
    0
    up

  5. #5
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Hi

    To remove numbers, try
    =TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE( SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTI TUTE(SUBSTITUTE(A2,"1",""),"2",""),"3",""),"4","") ,"5",""),"6",""),"7",""),"8",""),"9",""),"0","" ))
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  6. #6
    Senior Member
    Join Date
    Mar 2013
    Posts
    146
    Rep Power
    0
    Thank you mr admin , This formula is not working after the seventh condition

  7. #7
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Quote Originally Posted by mahmoud-lee View Post
    Thank you mr admin , This formula is not working after the seventh condition
    That is because you are using XL2003 which has a limit of 7 nested function calls (this limit was lifted in XL2007 and above). How about two UDFs (User Defined Functions)...

    Code:
    Function DigitsOnly(ByVal s As String) As Variant
      Dim X As Long
      For X = 1 To Len(s)
        If Mid(s, X, 1) Like "[!0-9]" Then Mid(s, X, 1) = Chr(1)
      Next
      DigitsOnly = Replace(s, Chr(1), "")
      If Len(DigitsOnly) < 16 Then DigitsOnly = Val(DigitsOnly)
    End Function
    
    Function NoDigits(ByVal s As String) As String
      Dim X As Long
      For X = 1 To Len(s)
        If Mid(s, X, 1) Like "#" Then Mid(s, X, 1) = Chr(1)
      Next
      NoDigits = Trim(Replace(s, Chr(1), ""))
    End Function
    HOW TO INSTALL UDFs
    ------------------------------------
    If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use NameOfTheUDF just like it was a built-in Excel function. For example, put the specified formula in the indicated cells...

    B3: =DigitsOnly(A3)

    C3: =NoDigits(A3)

    and then copy them down.
    Last edited by Rick Rothstein; 11-02-2013 at 07:47 AM.

  8. #8
    Administrator Admin's Avatar
    Join Date
    Mar 2011
    Posts
    1,123
    Rep Power
    10
    Quote Originally Posted by mahmoud-lee View Post
    Thank you mr admin , This formula is not working after the seventh condition
    Define a Name via Name Manager

    Select the first cell in your range (here I selected A2 on Sheet1)

    Name: String

    Refers to:
    PHP Code:
    =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Sheet1!$A2,1,""),2,""),3,""),4,""),5,""),6,""),7,""
    In B2:

    =TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(String,8,"" ),9,""),0,""))

    Note: The dollar sign should be there berore the column name.
    Cheers !

    Excel Range to BBCode Table
    Use Social Networking Tools If You Like the Answers !

    Message to Cross Posters

    @ Home - Office 2010/2013/2016 on Win 10 (64 bit); @ Work - Office 2016 on Win 10 (64 bit)

  9. #9
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    662
    Rep Power
    13
    Another possible formula for returning only the digits is this one posted previously in another forum by Lars-Ă…ke Aspelin...

    =MID(SUMPRODUCT(--MID("01"&A1,SMALL((ROW($1:$300)-1)*ISNUMBER(-MID("01"&A1,ROW($1:$300),1)),ROW($1:$300))+1,1),10 ^(300-ROW($1:$300))),2,300)

    This is an array formula and has to be confirmed with CTRL+SHIFT+ENTER rather than just ENTER.

    It has the following (known) limitations:

    - The input string in cell A1 must be shorter than 300 characters

    - There must be at most 14 digits in the input string.
    (Following digits will be shown as zeroes.)

    Maybe of no practical use, but it will also handle the following two cases correctly:

    - a "0" as the first digit in the input will be shown correctly in the output

    - an input without any digits at all will give the empty string as output (rather than 0).

  10. #10
    Senior Member
    Join Date
    Mar 2013
    Posts
    146
    Rep Power
    0
    THANK YOU Mr Admin
    THANK YOU Mr Rick

Similar Threads

  1. Get Field from Delimited Text String
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 5
    Last Post: 01-05-2017, 01:24 AM
  2. Replies: 7
    Last Post: 08-29-2013, 12:01 PM
  3. Remove Special Characters From Text Or Remove Numbers From Text
    By Excel Fox in forum Excel and VBA Tips and Tricks
    Replies: 5
    Last Post: 05-31-2013, 04:43 PM
  4. Replies: 14
    Last Post: 05-25-2013, 06:55 AM
  5. Extract Certain Characters From A Text String
    By bobkap in forum Excel Help
    Replies: 5
    Last Post: 05-24-2013, 06:25 AM

Posting Permissions

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