Risky macros - how to streamline and improve

[Adendum]

Hi Gurus,
I have 3 individual macros that I have been using but realise that sometimes these can be problematic.

What I need to do is after importing 200-300 rows of data is to move the last part of the text (Added dd mmm yyyy) from column A into column B. Ideally I would like to have the data in column B to be actual dates so instead of "Added 17 Jan 2021" it becomes 17/01/2021 (so I can later sort on column B). The problem I have found is that the imported data isn't always 17 characters - so "Added 5 Jan 2021" is only 16 whereas "Added 21 Jan 2021" is 17. So I am stumped!

For my education I would prefer to see examples that break down the processes. Any ideas?

Sub B_CopyAdded()
  For Each cell In Range("A2:A2000").Cells
    cell.Offset(0, 1).Value = Right(cell.Value, 17)
  Next cell
  Columns("A:A").Select
     
End Sub
Sub C_RemoveAdded()
' Deletes last 17 characters
' Defines variables
On Error Resume Next
    For Each cel In Range("A2:A2000")
        myVal = cel.Value
        cel.Value = Left(myVal, Len(myVal) - 17)
    Next cel
End Sub
Sub D_TrimCells()
Dim Cl As Range
   For Each Cl In Range("B2:B2000", Range("A" & Rows.Count).End(xlUp))
      Cl.Value = Trim(Cl.Value)
   Next Cl
End Sub

 

[Fluff]

Sounds like you have some odd characters in there, how about

Sub Adendum()
   With Range("A2", Range("A" & Rows.Count).End(xlUp))
      .Offset(, 1).Value = Evaluate(Replace("if({1},trim(mid(substitute(@,char(160),"" ""),search(""added"",@)+6,11)))", "@", .Address))
      .Value = Evaluate(Replace("if({1},trim(left(@,search(""added"",@&""added"")-1)))", "@", .Address))
   End With
End Sub

 

[Adendum]

Sounds like you have some odd characters in there, how about

Sub Adendum()
   With Range("A2", Range("A" & Rows.Count).End(xlUp))
      .Offset(, 1).Value = Evaluate(Replace("if({1},trim(mid(substitute(@,char(160),"" ""),search(""added"",@)+6,11)))", "@", .Address))
      .Value = Evaluate(Replace("if({1},trim(left(@,search(""added"",@&""added"")-1)))", "@", .Address))
   End With
End Sub

Hi Fluff,

I suspected something like that so I did dump my incoming data into Notepad so that I just had pure text but the same issue was occurring.

However, your last tweak has done the job! So, once again, many thanks.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Adendum]

Fluff,

A new discovery that is baffling me (as much does!!!).....

Most days we process data from the previous day, so today for example we process those entries with "Added 13 Apr 2021" so when we run the macro all is good. Similarly if we are working on the day when we have data showing "Added 7 Apr 2021" the macro works perfectly.

But sometimes we gather data that is a mix, like we did today, and when trying to process "Added 7 Apr 2021" and similar single date digits with those with double digit dates , e.g. "Added 17 Apr 2021" the macro fails.

When I say 'fail' I mean we get a mixed date style in the date column - all the double digit dates work properly and we get 17-Apr-2021 in the cells but the single digit values become text, so we get "7 Apr 2021".

So to be clear - the macro works if the incoming data is all single digit dates; it also works if all the incoming data is double digit dates. Mix the two and it breaks.

Can you resolve that?

 

[Fluff]

Works quite happily for me.

+Fluff 1.xlsm
	A	B
1
2	Fred Flintstone Added 9 Mar 2021 1 day overdue	09/03/2021
3	Barney Rubble Added 19 Mar 2021 1 day overdue	19/03/2021
4	Wilma Flintstone Added 20 Mar 2021 1 day overdue	20/03/2021
5	Betty Rubble Added 09 Mar 2021 1 day overdue	09/03/2021
Main

 

[Adendum]

Works quite happily for me.

+Fluff 1.xlsm
	A	B
1
2	Fred Flintstone Added 9 Mar 2021 1 day overdue	09/03/2021
3	Barney Rubble Added 19 Mar 2021 1 day overdue	19/03/2021
4	Wilma Flintstone Added 20 Mar 2021 1 day overdue	20/03/2021
5	Betty Rubble Added 09 Mar 2021 1 day overdue	09/03/2021
Main

Fluff,

And it does for me too - when I use the data I supplied here. But when we take the real incoming data it isn't working. So my guess is that there is something (code or hidden characters) that are affecting this process. The data comes from a web page, so there is a high possibility that 'crap' is getting in there. Although the copy process we use is to select the '123' option when pasting.

I can test the theory by pasting to Notepad first to see if that strips out garbage so I'll go away and do some more tests and will report back!