Page 1 of 2 12 LastLast
Results 1 to 10 of 16

How to remove numbers?

This is a discussion on How to remove numbers? within the Excel Questions forums, part of the Question Forums category; I'm trying to remove all numbers from text. I'm using =SUBSTITUTE(SUBSTITUTE(A1," ",""),"18945","") Number "18945" is just a sample. It could ...

  1. #1
    New Member
    Join Date
    Jul 2002
    Posts
    24

    Default

    I'm trying to remove all numbers from text.

    I'm using
    =SUBSTITUTE(SUBSTITUTE(A1," ",""),"18945","")

    Number "18945" is just a sample. It could be any number and 1 to 20 digits. It varies with each entry.

    Also how could ai add some default text to each entry.

    If I already have result of "Please Help" how could I add "ASAP" to each cell result?

    Thanks in advance.

    Ptrader


  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,950

    Default

    You could use a formula like this

    =MSUBSTITUTE(A1,"1234568790","")

    where MSUBSTITUTE is this UDF

    Function MSUBSTITUTE(ByVal trStr As Variant, frStr As String, toStr As String) As Variant
    ' Created by Juan Pablo González
    ' with ideas from Aladin Akyurek
    'toStr is assumed to be the same length of frStr. If not, the remaining characters
    'will be considered as null ("").

    'Note that this function IS case sensitive. To replace all instances of "a" you need
    'to use "a" AND "A"

    'You can't replace one character with two characters. This
    '=MSUBSTITUTE("This is a test","i","$@")
    'will result in this:
    '"Th$s $s a test"

    Dim iRow As Integer
    Dim iCol As Integer
    Dim j As Integer
    Dim Ar As Variant
    Dim vfr() As String
    Dim vto() As String

    ReDim vfr(1 To Len(frStr))
    ReDim vto(1 To Len(frStr))

    For j = 1 To Len(frStr)
    vfr(j) = Mid(frStr, j, 1)
    If Mid(toStr, j, 1) <> "" Then
    vto(j) = Mid(toStr, j, 1)
    Else
    vto(j) = ""
    End If
    Next j

    If IsArray(trStr) Then
    Ar = trStr
    For iRow = LBound(Ar, 1) To UBound(Ar, 1)
    For iCol = LBound(Ar, 2) To UBound(Ar, 2)
    For j = 1 To Len(frStr)
    Ar(iRow, iCol) = Application.Substitute(Ar(iRow, iCol), vfr(j), vto(j))
    Next j
    Next iCol
    Next iRow
    Else
    Ar = trStr
    For j = 1 To Len(frStr)
    Ar = Application.Substitute(Ar, vfr(j), vto(j))
    Next j
    End If
    MSUBSTITUTE = Ar
    End Function


    Edit: Ok Aladin, there it is...

    _________________
    Regards,

    Juan Pablo G.
    MrExcel.com Consulting

    [ This Message was edited by: Juan Pablo G. on 2002-07-30 12:30 ]

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    65,809

    Default

    On 2002-07-30 12:14, ptrader wrote:
    I'm trying to remove all numbers from text.

    I'm using
    =SUBSTITUTE(SUBSTITUTE(A1," ",""),"18945","")

    Number "18945" is just a sample. It could be any number and 1 to 20 digits. It varies with each entry.

    Also how could ai add some default text to each entry.

    If I already have result of "Please Help" how could I add "ASAP" to each cell result?

    Thanks in advance.

    Ptrader

    Could you give some examples of entries from which you want to remove digits?

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    65,809

    Default


    On 2002-07-30 12:19, Juan Pablo G. wrote:
    You could use a formula like this

    =MSUBSTITUTE(A1,"1234568790","")



    I was going to toss MSUBSTITUTE in. I notice you removed the author. Please edit your post and put that info in. Don't be shy...




  5. #5
    New Member
    Join Date
    Jul 2002
    Posts
    24

    Default

    Here are some sample entries.

    5832 Help should read just Help

    5942345 book is at home, should read just book is at home

    "3 rain today"should read just "rain today"

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    65,809

    Default

    On 2002-07-30 12:26, ptrader wrote:
    Here are some sample entries.

    5832 Help should read just Help

    5942345 book is at home, should read just book is at home

    "3 rain today"should read just "rain today"
    MSUBSTITUTE will take them off. As a matter of fact, it will "remove" every digit from each sentence. Give it a try. Let Juan explain how to add that function to your workbook.


  7. #7
    New Member
    Join Date
    Jul 2002
    Posts
    24

    Default

    Thanks Juan and Aladin.

    My Excel skill is newbie.

    Using Excel 97.

    Tried =MSUBSTITUTE(B1,"1234567890","")in formula bar but get error "#NAME?"


  8. #8
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,950

    Default

    Copy the provided code (Select it and hit Control C)

    Go to your workbook
    Hit Alt F11
    Select Insert | Module
    hit Control V

    now, go back to Excel, and try the formula again. That's not a builtin formula, but a UDF, user defined function.
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  9. #9
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    65,809

    Default

    On 2002-07-30 12:42, ptrader wrote:
    Thanks Juan and Aladin.

    My Excel skill is newbie.

    Using Excel 97.

    Tried =MSUBSTITUTE(B1,"1234567890","")in formula bar but get error "#NAME?"
    Juan is decided to let me sweat...

    ( 1.) Copy Juan's MSUBSTITUTE code.
    ( 2.) Open your target WB (close all others).
    ( 3.) Activate Tools|Macro|Visual Basic Editor.
    ( 4.) Activate Insert|Module.
    ( 5.) Paste the copied code in the window entitled "...(Code)".
    ( 6.) Activate File|Close and Return to Microsoft Excel.

    Now use:

    =IF(LEN(B1),TRIM(MSUBSTITUTE(B1,"1234567890","")),"")

    TRIM will remove any leading space after the action of MSUBSTITUTE.


  10. #10
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,950

    Default

    On 2002-07-30 12:51, Aladin Akyurek wrote:
    Juan is decided to let me sweat...

    Well, actually you're too slow...
    Regards,

    Juan Pablo González
    http://www.juanpg.com

Page 1 of 2 12 LastLast

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
  •  


DMCA.com