Consulting

Results 1 to 5 of 5

Thread: Removing leading letters in a text using VBA

  1. #1

    Removing leading letters in a text using VBA

    Hi,

    I am wondering id there is any function in VBA which can replace designated leading letters in a text with nothing.

    For example, let say in a cell I have "AAAAA235263", and I want to remove leading "AAAAA" and want to get only "235263".

    Any pointer will be very helpful.

  2. #2
    Knowledge Base Approver VBAX Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    p45cal
    Everyone: If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  3. #3
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,997
    Location
    That's a point well made P45cal. However since I'm still suffering the warm gooey feeling of Christmas here's a one last chance Volabos.

    Assuming your data in Cells A1: A3, and that you are running a Microsoft system, and also assuming I read the data right off the video here's a starter for you. I can't test this as a Mac can't run CSE entered formulas so here goes;

    In Cell B1 write the following formula: =Left(A1,MATCH(TRUE,ISERROR(VALUE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),0 )))-1))

    In Cell C1 write the following formula: =Right(A1,(LEN(A1)-((MATCH(TRUE,ISERROR(VALUE(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1))),0)))+1))

    Hopefully this strips the Alphabeticals from the Numerals within the string.
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  4. #4
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    4,997
    Location
    Public Notice: volabos
    Yes, there are some suggestions provided by other experts. I can work with these suggestions.
    pm.

    Given that only one solution was provided here, and we don't yet know if either of the two formulas as presented were correct, how can anyone then mark the Thread as solved? I notice also that yet again no acknowledgement of the effort has been offered, despite a very clear warning from others. Let this be a lesson to all people who post here in this forum.... if you haven't got the ethical courage to thank others who try to assist, then please feel free to use another forum. volabos don't let the door hit you on the way out.
    Remember To Do the Following....
    Use [Code].... [/Code] tags when posting code to the thread.
    Mark your thread as Solved if satisfied by using the Thread Tools options.
    If posting the same issue to another forum please show the link

  5. #5
    you can also use RegExp:
    Public Function GetNumeric(ByVal var As Variant)
        Static oReg As Object
        GetNumeric = 0
        If IsNull(var) Then
            Exit Function
        End If
        On Error GoTo Create_Object
        var = var & ""
        With oReg
            .Pattern = "([^0-9])"
            .Global = True
            GetNumeric = Val(.Replace(var, ""))
        End With
        Exit Function
    Create_Object:
        Set oReg = CreateObject("VbScript.RegExp")
        Resume Next
    End Function

Posting Permissions

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