Removing some characters from a cell depending on a pattern.

zookeepertx

Well-known Member
Joined
May 27, 2011
Messages
507
Office Version
  1. 365
Platform
  1. Windows
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 InvoiceOriginDestCartonsHAWBPOShipperVendor Called for BookingCargo Ready DateShipment 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.

VBA Code:
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
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,553
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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

Well-known Member
Joined
May 27, 2011
Messages
507
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,553
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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...
VBA Code:
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
 
Last edited:

zookeepertx

Well-known Member
Joined
May 27, 2011
Messages
507
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,553
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,553
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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...
VBA Code:
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
 
Solution

zookeepertx

Well-known Member
Joined
May 27, 2011
Messages
507
Office Version
  1. 365
Platform
  1. Windows
YIPPEEE!! That is 100% perfect! You're awesome!

Have a great rest of the day.

Jenny
 

Forum statistics

Threads
1,148,159
Messages
5,745,118
Members
423,925
Latest member
globaltlg

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top