Neat trick?

[Gasman]

On another forum, a member was asking how to replace a character at a certain location.
Replace() was offered, as you would expect.
The query was
These are 2 examples

02/26/2006N191 Merrimac Trl # 10

A10/26/2004N4059 Thorngate Dr

The Ns need to simply be changed to an O. No other change needed.

However a latter solution offered was
Mid(strTest, 12, 1) = "O" 'Replaces the 12th character 'N' with 'O'

which at least I had never heard of.

 

[Grumm]

Well it is documented in the Microsoft docs : https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/mid-statement

But using stuff like that is dangerous. It will always replace the 12th character with an 'O'.
Your first example will be wrong : 02/26/2006N191 Merrimac Trl # 10 --> The N is here at the 11th position.
Is that what is wanted ? If not, a better solution is to replace the first N found from the left. (Depends of how the data is made ofcourse)

 

[Gasman]

That is what was required. They were not my examples bu the o/p's
If they cannot count, that is their problem.
They particularly did not want any previous N being amended. Of course the Replace() wwould do all that as well, but I still thought it was neat.
Here is the thread is anyone is interested https://www.utteraccess.com/topics/2063733/posts/2803519

Thanks for the link.

 

[gemma-the-husky]

Yes, most people use mid as a function on the RHS of an expression to return part of a string, rather than the alternative as a standalone function/procedure to replace specific characters in a string.

The second is useful when you have a precise understanding of the string structure, and avoids the need for multiple string-slicing operations.

 

[arnelgp]

pg.26 VBA Developer's Handbook
Ken Getz / Mike Gilbert
2000