Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: HELP - TO FIND AND REMOVE BITS OF TEXT

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    have a list of numbers which i need to remove
    the text characters from,like
    485P6
    25M2
    29306P3
    3249RP108
    25AM2
    need to remove character codes 65 thru 90
    somehow? thanks in advance

    [ This Message was edited by: eddievegas on 2002-04-08 15:09 ]

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,619
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-04-08 15:06, eddievegas wrote:
    have a list of numbers which i need to remove
    the text characters from,like
    485P6
    25M2
    29306P3
    3249RP108
    25AM2
    need to remove character codes 65 thru 90
    somehow? thanks in advance

    [ This Message was edited by: eddievegas on 2002-04-08 15:09 ]
    Eddie,

    Lets assume that A1:A5 houses the sample you provided:

    In B1 enter and copy down:

    =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,0,""),1,""),2,""),3,""),4,""),5,""),6,""),7,"")

    In C1 enter and copy down:

    =SUBSTITUTE(A1,SUBSTITUTE(SUBSTITUTE(B1,8,""),9,""),"")+0

    If you don't want to have the results to be number-formatted, just drop +0 from the latter formula.

    Caveat. It will not work on strings like 45WQ423U.

    Aladin


    [ This Message was edited by: Aladin Akyurek on 2002-04-08 16:30 ]

  3. #3
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Eddie,

    I assume you mean the ASCII character codes 65-90 that encompass all upper case standard alphabet characters. Here is a bit of code that I wrote to clean up control characters (it turned out to be totally unnecessary as there is a CLEAN function already in Excel), but the serendipitous result is that it should work fine for your problem. Just select the range you want "cleaned" and run the CleanSelectedRange macro. This code should be placed in a standard macro module.

    Sub CleanSelectedRange()
    ' "Cleans" contents of all selected cells on the active worksheet
    Dim Cell As Range
    For Each Cell In Selection
    If Not Cell.HasFormula Then
    Cell.Value = CleanString(Cell.Value)
    End If
    Next Cell
    End Sub

    Function CleanString(StrIn As String) As String
    ' "Cleans" a string by removing embedded characters in the ASCII range from 65-90
    'This function runs recursively, each call
    ' removing one embedded character
    Dim iCh As Integer
    Dim ACh As Integer
    CleanString = StrIn
    For iCh = 1 To Len(StrIn)
    ACh = Asc(Mid(StrIn, iCh, 1))
    If 64 < ACh and ACh < 91 Then
    'remove designated character
    CleanString = Left(StrIn, iCh - 1) & CleanString(Mid(StrIn, iCh + 1))
    Exit Function
    End If
    Next iCh

    End Function
    Keep Excelling.

    Damon

    VBAexpert Excel Consulting
    LinkedIn Profile http://www.linkedin.com/pub/damon-ostrander/7/79/a93
    AllExperts Profile http://www.allexperts.com/ep/1059-30...-Ostrander.htm

Some videos you may like

User Tag List

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
  •