replace characters based on specified position number

[anski]

how do i replace a character that is the 10th character from the right? 2nd character from the left, etc.

i have read about sql STUFF function. does access have a similar function?

 

[isladogs]

If you are replacing a specific character e.g. X in a string (strText) with e.g. Y then just use

Replace(strText,"X","Y")

However, if the character to be replaced may change then one way of doing this is
a) 2nd character from left

Left(strText,1) & "Y" & Mid(strText,3)

b) 10th character from right

Left(strText, Len(strText) - 11) & "Y" & Mid(strText, Len(strText) - 9)

If you are going to replace a variety of different position characters, I would make these into functions so the character position to be replaced can be varied

 

[arnelgp]

Mid(string,2,len(string)-10)

 

[isladogs]

Mid(string,2,len(string)-10)

How exactly does this answer the question set?
Its not an Access equivalent to the useful SQL STUFF function
Nor is is it a REPLACE equivalent

The OP wants to replace characters - not just get a list from 2nd character on left to 10th from the right

 

[The_Doc_Man]

Colin, actually there is a little "loophole" in the way that MID$ works. The following code in my Ac2010 will perform a positional substitution at a specific point in the middle of a string. And before you ask, I did it this way so the variables would show up in a Locals window for tracing purposes. It works in other contexts, too.

Public Sub TestMid(ByRef X As String, Y As String, Z As Long)

Mid$(X, Z, Len(Y)) = Y

End Sub

To test it, I used this code:

    sX = "ABCDEF"
    sY = "RS"
    TestMid sX, sY, 3

After execution, this is what I see via cut/paste from the Locals window inside that subroutine (though I edited out some spaces to keep it on one line):

X                  "ABRSEF"                String

In essence, you can drop a sub-string into the middle of a larger string at a fixed position using the MID$ function on the left-hand side of an equals-sign/expression. You can play with it, but this bizarre situation actually works. And NO, it does not give a compiler error and NO, it doesn't give a run-time error. I suspect it wouldn't be quite so well behaved if the string to be inserted was larger than the space left in the original string after the offset starting point.

I have not tried the SQL version of this but anyone who wants to try it should be able to test it easily enough. Works fine in VBA, though.

 

[arnelgp]

Ok.


string = Replace(string, Mid(string,2,len(string)-10), replacementString, 1, 1)

 

[isladogs]

@Doc
I've read about unusual ways of using Mid to imitate the Stuff/Replace functions direct but can't get your code to work.
I believe I copied it exactly as described:

Function TestMid(ByRef X As String, Y As String, Z As Long)
Mid$(X, Z, Len(Y)) = Y
End Function

Sub CheckTestMid()
  Dim sX As String, SY As String
  sX = "ABCDE"
  SY = "RS"
  Debug.Print TestMid sX, SY, 3
      
End Sub

I also tried Sub for TestMid - it needs to be a function

When I type the Debug.Print line, Access replaces it with

TestMid[COLOR="Red"];[/COLOR] sX, SY, 3

Unsurprisingly, running it gives a compile error - argument not optional

I even tried Debug.Print TestMid(sX, SY, 3) though I knew it was incorrect. No error but nothing happens
Haven't looked at Watch window as yet

@arnelgp
Your new version correctly replaces all characters between the 2nd on left & 10th on right with a replacement string.
That's fine IF that's what the OP wants... but that isn't how I read post 1

@anski
Are you reading these replies & have you got what you want from any of them?

 

[Gasman]

A simple Debug.Print sX shows the result though?

 

[isladogs]

A simple Debug.Print sX shows the result though?

No the Access gnomes won't allow that
EDIT - they've just relented and let me do so - and yes it worked .... with or without the $ in the original function

 

[arnelgp]

Another variation:

string = Replace(string, Mid(string,2,len(Mid(string, 2))-10), replacementString, 1, 1)

 

[1268]

Surprised to find more than one way to do something in access....

*sigh

Sent from my SM-G950U using Tapatalk

 

[isladogs]

Surprised to find more than one way to do something in access....

*sigh

Sent from my SM-G950U using Tapatalk

LOL. Back in post 2 I said 'one way of doing this is .....'

There are always lots of solutions, some perhaps better than others.

 

[The_Doc_Man]

Colin, one difference was that my function was NOT a function, nor was it declared as such. It was a SUB using a ByRef argument for the string that was the target of the substitution. I was using a 32-bit version of Ac2010. What you wrote, as a function, would not return a value because you never assigned a value to TestMid.

As I said, the way I did it was based on using the Locals window to verify the effect, so that is why all the gyrations. But it worked just fine for me once I got my invariably present typos fixed.

What I presented does the positional replace in-place. I didn't test what happens if the inserted string is longer than or overlaps the end of the target string, but that wasn't the point. Also did not test for the case where the string to be inserted did not match the actual size argument of the Mid$. I just wanted to demonstrate that it was possible.

By the way, Mid$ only returns strings; Mid without the $ returns variants. I don't know whether that is significant to this experiment.

 

[1268]

LOL. Back in post 2 I said 'one way of doing this is .....'

There are always lots of solutions, some perhaps better than others.

Right?!? Access can be so open ended, there are often many good ways to do something, a few poorer options, and sometimes even a most right way, but there is almost never one way [emoji16]

Sent from my SM-T813 using Tapatalk

 

[isladogs]

Colin, one difference was that my function was NOT a function, nor was it declared as such. It was a SUB using a ByRef argument for the string that was the target of the substitution. I was using a 32-bit version of Ac2010. What you wrote, as a function, would not return a value because you never assigned a value to TestMid.

As I said, the way I did it was based on using the Locals window to verify the effect, so that is why all the gyrations. But it worked just fine for me once I got my invariably present typos fixed.

What I presented does the positional replace in-place. I didn't test what happens if the inserted string is longer than or overlaps the end of the target string, but that wasn't the point. Also did not test for the case where the string to be inserted did not match the actual size argument of the Mid$. I just wanted to demonstrate that it was possible.

By the way, Mid$ only returns strings; Mid without the $ returns variants. I don't know whether that is significant to this experiment.

Thanks for explaining the difference between Mid$ & Mid.
Presumably the same is true for Left$ & Left etc

I did originally copy your code EXACTLY using a SUB. I only changed to a function when I couldn't get that to work as a sub.

As I said in a correction to an earlier reply:

EDIT...yes it worked .... with or without the $ in the original function

and that's was with TestMid as a function.
Just restested & its now working as a sub also

As is often the case, we've taken this thread a long way since the OP.
Hopefully at some point anski will return to read all of it!

 

[The_Doc_Man]

At least he can see a way to do an in-place substitution at a specific offest, which WAS the whole point of the thread.

 

[anski]

If you are replacing a specific character e.g. X in a string (strText) with e.g. Y then just use

Replace(strText,"X","Y")

However, if the character to be replaced may change then one way of doing this is
a) 2nd character from left

Left(strText,1) & "Y" & Mid(strText,3)

b) 10th character from right

Left(strText, Len(strText) - 11) & "Y" & Mid(strText, Len(strText) - 9)

If you are going to replace a variety of different position characters, I would make these into functions so the character position to be replaced can be varied

I get the logic of the above and I tried it as soon as I read your reply. This only changes the LAST ROW of my file. I have 5 rows and the first 4 rows are not affected (but they should be). This is a portion of my code:

sFileName = CurrentProject.Path & "" & strCsv
iFileNum = FreeFile
Open sFileName For Input As iFileNum

Do Until EOF(iFileNum)
Line Input #iFileNum, sBuf
sTemp = sTemp & sBuf & vbCrLf
Loop
Close iFileNum

sTemp = Replace(sTemp, Left(sTemp, Len(sTemp) - 11), Left(sTemp, Len(sTemp) - 11) & "T" & Mid(sTemp, Len(sTemp) - 9))

If Right(sTemp, 2) = Chr(13) & Chr(10) Then
sTemp = Left(sTemp, Len(sTemp) - 2)
End If 'to delete of one blank row at the end of the file

iFileNum = FreeFile
Open sFileName For Output As iFileNum
Print #iFileNum, sTemp
Close iFileNum

(I went back to this post and saw other advice but I find yours more "understandable" coming from a non-programmer.)

 

[isladogs]

This only changes the LAST ROW of my file. I have 5 rows and the first 4 rows are not affected (but they should be).

Ah - so you are trying to update an external csv file which is slightly more complex

Can you either show a screenshot of the item to be updated & what the result should be
OR post a stripped down version of the database & csv file with just the relevant parts

 

[anski]

Before - Without Replace code
After - With Replace code

The following code works (replaces affected text in ALL rows):

sTemp = Replace(sTemp, ",""DUMMY""", "")

I cannot figure out why the one I wrote (with the left, len, mid functions) does not affect all the rows.

 

[isladogs]

Ah... still working on the dummy.

If the original code worked (which it will), why try & do this method instead?

Access is correctly seeing this as one file not as separate rows
It only modifies the last 'row' as that's the only one fitting your criteria

Its either going to need looping through each line in turn
... or there may well be a better approach e.g. what you had before!

Otherwise, as its quite a complex csv file, it would be easier if you could upload the file for testing a solution.
I won't be able to look at it for a few hours & haven't yet studied your code.
However, someone else will probably do so before then,