CODEWITHSUNDEEP
stringvbaexcel
Joseph Bay
Joseph Bay

Reputation: 69

VBA Finding Numbers, Letters, and Characters in Cell and Replacing Content of Cell With Only Numbers/Letters

Having another problem.

I am creating another Excel tool at my facility and currently what I have pulls data from a purchase order tracking website and filters out superfluous data, leaving only the purchase order label itself, a single quotation mark, and an end bracket. I need to remove the quotation mark and ending bracket so I only have the PO itself, as I need this to inject into another site's URL. I have tried using wildcards with some code I wrote however the PO will get replaced with several asterisks, aka "wildcards," instead. I am probably overlooking something obvious, but I can't figure it out.

Example of data:

enter image description here

Code example:

Sub Filter_DockMaster_Data2()

Dim Main As Worksheet
    Set Main = Worksheets("Main")
Dim ISA_List As Worksheet
    Set ISA_List = Worksheets("ISA_List")
Dim ISA_Results As Worksheet
    Set ISA_Results = Worksheets("ISA_Results")
Dim ISA_Raw As Worksheet
    Set ISA_Raw = Worksheets("ISA_Raw")

Worksheets("ISA_Results").Select
Range("A1").Select
    Do Until IsEmpty(ActiveCell)
        ActiveCell.replace What:="********"" ]", Replacement:="********", LookAt:= _
            xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
            ReplaceFormat:=False
        ActiveCell.Offset(1, 0).Select
    Loop

End Sub

Hopefully this makes sense.

Quick notes, the length of the PO will vary as time goes on, so I would like to make it dynamic if possible. Any help is appreciated, thanks.

Upvotes: 3

Views: 728

Answers (2)

user4039065
user4039065

Reputation:

Your problem shouldn't be about repairing the the data you've extracted from some source. It really should be about fixing the retrieval procedure so you do not get rogue characters.

With that said, this sub procedure should quickly remove any rogue characters from column A.

Option Explicit

Sub posOnly()

    Dim i As Long, str As String, rgx As Object

    Set rgx = CreateObject("VBScript.RegExp")
    'pattern for A-Z (case sensitive) or 0-9 exactly 8 characters/digits in length
    rgx.Pattern = "[A-Z0-9]{8}"

    With Worksheets("ISA_Results")
        For i = 2 To .Cells(.Rows.Count, "A").End(xlUp).Row
            str = .Cells(i, "A").Value2
            If rgx.Test(str) Then
                str = rgx.Execute(str).Item(0)
                .Cells(i, "A") = str
            End If
        Next i
    End With

End Sub

Upvotes: 1

ashleedawg
ashleedawg

Reputation: 21639

Encode for a URL

How about the function intended for making text URL-friendly? :)

=ENCODEURL(A1)

For example, this:

1234ABCD']

...becomes encoded as: 

1234ABCD%27%5D

...ready to insert in a query parameter (URL) string. See the documentation including further examples at at the link below.

Text Functions like LEFT

That said, there are several other ways to do this.

You said "replace" but it looks like you just need to cut off the last 2 characters?

This cuts the last 2 characters of the text in A1:

=LEFT(A1,LEN(A1)-2)

SUBSTITUTE Function

If you do want to "replace" text in a cell, you can use SUBSTITUTE:

Example:

If cell A1 contains:

1234ABCD']

...you could enter in another cell:

=SUBSTITUTE(A1,"]","")

...which would remove only the ]. You can also nest functions. To remove both the ] and the ', use this formula instead:

=SUBSTITUTE(SUBSTITUTE(A1,"]",""),"'","")

The SUBSTITUTE function syntax:

SUBSTITUTE( text, old_text, new_text, [instance_num] )

The SUBSTITUTE function syntax has the following arguments:

  • Text - (Required) The text or the reference to a cell containing text for which you want to substitute characters.
  • Old_text - (Required) The text you want to replace.
  • New_text - (Required) The text you want to replace old_text with.
  • Instance_num - (Optional) Specifies which occurrence of old_text you want to replace with new_text. If you specify instance_num, only that instance of old_text is replaced. Otherwise, every occurrence of old_text in text is changed to new_text.

(Source & More Info)

More Information:

Upvotes: 2

Related Questions