replace characters based on specified position number (1 Viewer)

anski · May 26, 2018

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 · May 26, 2018

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

Code:

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

Code:

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

b) 10th character from right

Code:

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 · May 27, 2018

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

isladogs · May 27, 2018

arnelgp said:
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 · May 27, 2018

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.

Code:

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:

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):

Code:

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 · May 27, 2018

Ok.

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

isladogs · May 27, 2018

@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:

Code:

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

Code:

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 · May 27, 2018

A simple Debug.Print sX shows the result though?

isladogs · May 27, 2018

Gasman said:
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 · May 27, 2018

Another variation:

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

1268 · May 27, 2018

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

*sigh

Sent from my SM-G950U using Tapatalk

isladogs · May 27, 2018

1268 said:
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 · May 27, 2018

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 · May 27, 2018

ridders said:
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 · May 27, 2018

The_Doc_Man said:
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 · May 27, 2018

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 · May 28, 2018

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

Code:

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

Code:

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

b) 10th character from right

Code:

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 · May 28, 2018

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 · May 28, 2018

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 · May 28, 2018

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,

replace characters based on specified position number (1 Viewer)

Registered User.

MVP / VIP

..forever waiting... waiting for jellybean!

MVP / VIP

Immoderate Moderator

..forever waiting... waiting for jellybean!

MVP / VIP

Enthusiastic Amateur

MVP / VIP

..forever waiting... waiting for jellybean!

Registered User.

MVP / VIP

Immoderate Moderator

Registered User.

MVP / VIP

Immoderate Moderator

Registered User.

MVP / VIP

Registered User.

Attachments

MVP / VIP

Similar threads

Users who are viewing this thread