Simplified Search and Replace

[CDandVinyl]

Hi All

In H6:H300 I have text in the form

01/04/2018..... (F32)
25/04/2018..... (F37)
03/05/2018..... (F40)
11/05/2018..... (F44)
16/05/2018..... (F46)
04/07/2018..... (F62)
10/07/2018..... (F64)
31/07/2018..... (F71)
08/08/2018..... (F74)


I'm trying via VBA to search and replace the first number to include ordinal suffixes , and to replace the second number with the month in full. Spaces would replace obliques.

So the above would become :

1st April 2018..... (F32)
25th April 2018..... (F37)
3rd May 2018..... (F40)
11th May 2018..... (F44)
16th May 2018..... (F46)

and so on.

I think a really really long search and replace routine would do it , but I'm hoping it can be done with something much neater and quicker.

I don't know if it's a complicating factor but H6:H300 are merged with their neighbours in column I. All the relevant cells have General formatting.

Grateful for any advice.

[ikboy]

Please Login or Register  to view this content.

[CDandVinyl]

Many thanks for this. I'm beyond impressed , I have to say.

It's working perfectly, although it gives ordinals 21th and 31th.



BTW - Strangely , when I run the code as a separate macro , it runs through cleanly. When I incorporate the code into an existing macro (minus the 'Sub zz()' and 'End Sub' lines) , it gives an 'unknown' error for variable i. I added i to the Dim list and it runs fine. Curious....

[jindon]

Meaningless use of Regular Expressions...
Try

Please Login or Register  to view this content.

[Rick Rothstein]

Please Login or Register  to view this content.

What I highlighted in red above can be replaced by this single line of code (which will work correctly for any number n, not just a date's day value)...

s =n & Mid$("thstndrdthththththth", 1 - 2 * (n Mod 10) * (Abs(n Mod 100 - 12) > 1), 2)

Meaningless use of Regular Expressions...
Try

Please Login or Register  to view this content.

Similar to my above comment to ikboy, the above red highlighted code can be replaced by this single line of code if desired...

sufx = Mid$("thstndrdthththththth", 1 - 2 * (x(0) Mod 10) * (Abs(x(0) Mod 100 - 12) > 1), 2)



NOTE
-----------------------
For those who might be interested, given the way Excel's MOD function handles negative numbers, the ordinal suffix for a number (any number, not just a date's day number) in cell A1 can be calculated using this "simpler" expression for a formula in a cell...

=A1&MID("thstndrdth",MIN(9,2*RIGHT(A1)*(MOD(A1-11,100)>2)+1),2)

where the part highlighted in blue includes the number itself (so if A1 contains 11, the formula returns 11th, not just th).

[jindon]

So what's wrong???

I use Switch/Choose function instead, if I need to change...

[Rick Rothstein]

I didn't say anything was wrong... I was just providing an alternative for future readers who might find what I posted interesting. For the record, I do not like VB's Switch and Choose functions as they are fairly "slow" functions compared to their multi-line equivalents (plus one has to be wary if using expressions that might produce an error). By the way, in case you might be interested, I added an Excel formula equivalence (shorter than the VB version) as a note to Message #5 after you had posted your comment in Message #6. And in case you don't find it interesting, my thought is that a future reader of this thread might.

[jindon]

No need to refer my codes, just post your solution, if you want to.

Nothing wrong with Select case statement in speed. One liner is not always fastest all the time.

[CDandVinyl]

Please Login or Register  to view this content.

Just for my own education , which element of the code changes the numbers below 10 to one digit rather than two?

Before running the code the numbers are

01 , 02 , 03 , 04

and so on.

After the code they are

1st , 2nd , 3rd , 4th.

How could the code be modified to be 01st , 02nd , 03rd ....?

Just curious.

Thanks for your time and considerable expertise.

[jindon]

Val function converts number as string to numeric numbder, so beginning 0 will be removed.
If you remove Val function, it gives you 2 digit numbers as they are.

Please Login or Register  to view this content.