Removing some characters from a cell depending on a pattern.

[zookeepertx]

Hello all!
I have a question that I posed a while back and received excellent help on! But now I've been informed that there a few new scenarios that need to be handled and I can't figure out how to get the macro to recognize them.
I'm at work and am not allowed to download any applications, etc that would allow me to post a spreadsheet here, so I'll have to show a table with the data and hope you all don't mind that.

Here's an example of my raw data. I'm only concerned with the data in column F (PO), so I'll leave the other columns blank, just to make it less cluttered.
Below that I'll post my original request and the new problem and the solution code I was given to take card of the original request:

Import Invoice	Origin	Dest	Cartons	HAWB	PO	Shipper	Vendor Called for Booking	Cargo Ready Date	Shipment Pickup
					10100591452/8113 10100601841/8113 10100601841 10100591452
					9289 10100612579/2026 10100612579
					NONE
					10100610339 10100610339-7079
					DN-02608 10100598256 10100598256/4447
					NA 10100622117 10100622117/2025
					NA 10100559684
					10100620443 NA
					NA 10100601761/2026 10100601761
					10100563829 10100563829/8164
					10100563829
					DN-32039 10100563829

The existing code below correctly removes that information that I've colored purple in the table above. My problem is that I need to ALSO get rid of the information that I've colored red in the table. I only need to keep the information that's colored black in the table.

Set r = Range("F2", Range("F" & Rows.count).End(xlUp))
With CreateObject("VBScript.RegExp")
     .Global=True
     ForEachrCInr
          .Pattern="\d{11}/\d{4}|\d{4}/\d{11}"
               s=.Replace(rC.Value,"")
          .Pattern="(\d{11})(.+)(\1)"
          While.Test(s)
               s=.Replace(s,"$1$2")
          Wend
          rC.Value=Application.Trim(Replace(s,"PO",""))
     NextrC
End With

I've tried numerous adjustments in the code to handle the new patterns in the data (which I wish my co-worker had told me about in the beginning), but am getting absolutely nowhere. Either it has no effect at all or it causes an error message.

Just as a side note, in the existing code, I understand .Pattern="\d{11}/\d{4}|\d{4}/\d{11}" but I don't get what .Pattern="(\d{11})(.+)(\1)" is looking for. If someone could enlighten me on that, it'd be great!

I really appreciate any help you can give me! This board has saved my bacon so many times already, too!

Jenny

 

[Rick Rothstein]

Please clarify: Is your "rule" for the additional removals...

1) values that start with "DN-"?

2) Words without digits or specifically the words "NA" and "NONE"?

What I can't invent a "rule" for given your single example is why "9289" is being removed from cell F3. Please explain why it is being removed.

And for clarification... am I right in assuming your original "rule" was to remove all digits with a slash or dash in them?

 

[zookeepertx]

Hi,
Thanks for your reply!

1) DN- For example, cell F6 needs to end up with only the 11 digit number - 10100598256 - in it, just 1 time. Cell F13 needs to end up with just the 11 digit number - 10100563829 - in it.

2) Words without digits - Yes, "NA" and "NONE" need to go away, even if it leaves the cell empty. In fact any and all letters need to go away.

3) Cell F3 - The only thing that needs to remain in that cell is the 11 digit number - 10100612579. So the 4 digit number "9289" needs to go and the duplicate of 10100612579/2026 needs to go.

4) Yes, the original request posed by my co-worker - and that I posted - was to remove all digits with a slash or a dash in them. (It didn't occur to me until recently that only the 4 digits with a slash were being deleted; the ones with 4 digits and a DASH were not being deleted. So I've tried to fix that, too, with no luck).
Also, any time "PO" appears in a cell - before or after the 11 digit number - "PO" needs to go away. That is also working correctly.

But then, the co-worker informed me of the additional cases that might appear in cells, such as 2 letters a dash and 5 digits, or just letters being in a cell.

Thank you for taking a look at this.
Jenny

 

[Rick Rothstein]

3) Cell F3 - The only thing that needs to remain in that cell is the 11 digit number - 10100612579. So the 4 digit number "9289" needs to go and the duplicate of 10100612579/2026 needs to go.

Ah! I think I see now. It is not a case of what specifically should be removed, rather is that only 11-digit numbers should remain and any thing else that is not an 11-digit number should be removed... is that correct? Assuming the answer is "Yes", then give this non-RegExp macro a try...

Sub ElevenDigitsOnly()
  Dim R As Long, N As Long, Cell As Range, Data As Variant, Arr As Variant
  Data = Range("F2", Cells(Rows.Count, "F").End(xlUp))
  For R = 1 To UBound(Data)
    Arr = Split(Replace(Data(R, 1), Chr(160), " "))
    For N = 0 To UBound(Arr)
      If Not Arr(N) Like "###########" Then Arr(N) = ""
    Next
    Data(R, 1) = Application.Trim(Join(Arr))
  Next
  Range("F2").Resize(UBound(Data)) = Data
End Sub

 

[zookeepertx]

Oooh! That's virtually perfect!! I only see 1 thing and I don't even care about it, LOL!
I created some new data, just to see what would happened and discovered that, if on PO is in a cell THREE times, one gets removed (for example: NA 10100559684 10100559684 10100559684/1234 ends up as 10100559684 10100559684). And if the duplicate number is in the cell but not right beside the first instance, they both stay ( for example: 10100620443 NA 10100559684 10100620443 ends up as 10100620443 10100559684 10100620443)
Those are more rare occasions so won't happen with any frequency and all of the rest of it is just perfect.
The people that will be using this, can handle that. ?

Thank you SO much for the clean, concise code; love it!

Jenny

 

[Rick Rothstein]

I can handle this in my code but I need to know what should happen with duplicates... only one remains from the duplicated values or they just need to be sorted next to each other?

 

[Rick Rothstein]

I can handle this in my code but I need to know what should happen with duplicates... only one remains from the duplicated values or they just need to be sorted next to each other?

Assuming you want to remove duplicates so only one remains, give this modified version of my code a try...

Sub ElevenDigitsOnly()
  Dim R As Long, N As Long, Cell As Range, Data As Variant, Arr As Variant
  Data = Range("F2", Cells(Rows.Count, "F").End(xlUp))
  With CreateObject("Scripting.Dictionary")
    For R = 1 To UBound(Data)
      Arr = Split(Replace(Data(R, 1), Chr(160), " "))
      .RemoveAll
      For N = 0 To UBound(Arr)
        If Not Arr(N) Like "###########" Then
          Arr(N) = ""
        Else
          .Item(Arr(N)) = 1
        End If
      Next
      Data(R, 1) = Application.Trim(Join(.Keys))
    Next
  End With
  Range("F2").Resize(UBound(Data)) = Data
End Sub

 

[zookeepertx]

YIPPEEE!! That is 100% perfect! You're awesome!

Have a great rest of the day.

Jenny