Change Characters

silentwolf

Well-known Member
Joined
May 14, 2008
Messages
1,216
Office Version
  1. 2016
Hi guys,

just need to get this function running but struggling at present.
VBA Code:
Function ChangeCharacters(ByVal strText As String) As String
    Dim intPos As Integer
    intPos = InStrRev(strText, ".")
    
    Const c_Sonder As String = " -.,_:;#+ß'*?=)(/&%$§!~\}][{"
    Dim i As Integer
    
    For i = 1 To Len(c_Sonder)
        strText = Replace(strText, Mid(c_Sonder, i, 1), "_")
    Next i
    
    ChangeCharacters = strText
End Function
with this code above found here the problem I have is that I just like to replace the characters only till the last dot.
Therefor I did use the varible intPos as above but how to I need to adapt it to my Replace function.

for example.... strText="4141_01-12-2019.xlsx" so I like to have the outcome "4141_01_12_2019.xlsx"

So the last Dot not changed.

Thanks for help!
Albert
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
@silentwolf Albert, try this...

VBA Code:
Function ChangeCharacters(ByVal strText As String) As String
    Dim intPos As Integer
    Dim LStr, RStr As String
    intPos = InStrRev(strText, ".")
    LStr = Left(strText, intPos - 1)
    RStr = Mid(strText, intPos, 999)
    Const c_Sonder As String = " -.,_:;#+ß'*?=)(/&%$§!~\}][{"
    Dim i As Integer
    
    For i = 1 To Len(c_Sonder)
        LStr = Replace(LStr, Mid(c_Sonder, i, 1), "_")
    Next i
    
    ChangeCharacters = LStr & RStr
End Function

Hope that helps.
 
Upvote 0
Hi,
fantastic :)
Thank you very much ! New that someone is outthere who knows to help ;)

Many thanks
Nice greetings
 
Upvote 0
Will the character "-" sometimes appear after the last "." ?
If not, maybe a worksheet formula could be used ? : =SUBSTITUTE(A1,"-","_")
 
Upvote 0
Will you really have any of those other characters apart from "-" & "_" in your one example?

If so, and if you are interested, here is a way to replace them all at once (ie no loop) & without separately finding the position of that last "."

VBA Code:
Function ChangeChars(ByVal strText As String) As String
  Dim strTemp As String
  Dim RX As Object
  
  Const c_Sonder As String = " -.,_:;#+ß'*?=)(/&%$§!~\}][{"
  
  strTemp = Join(Split(StrConv(c_Sonder, vbUnicode), Chr(0)), "\")
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.Pattern = "[\" & Left(strTemp, Len(strTemp) - 1) & "](?=.*\.[^\.]+)"
  ChangeChars = RX.Replace(strText, "_")
End Function

silentwolf 2020-04-11 1.xlsm
AB
14141_01-12-2019.xlsx4141_01_12_2019.xlsx
2abc -.,_:;#+ß'*?=)(/&%$§!~\}][{.xyzabc____________________________.xyz
Sheet1
Cell Formulas
RangeFormula
B1:B2B1=ChangeChars(A1)
 
Upvote 0
Or try:
PHP:
Function ChangeCharacters(ByVal strText As String) As String
    
    Const c_Sonder As String = " -.,_:;#+ß'*?=)(/&%$§!~\}][{"
    Dim i As Integer
    
    For i = 1 To InStrRev(strText, ".") - 1
        If InStr(1, c_Sonder, Mid(strText, i, 1)) > 0 Then Mid(strText, i, 1) = "_"
    Next i
    
    ChangeCharacters = strText
End Function
 
Upvote 0
Hi guys"
wow so many have replied to my question .. nice!
@footoo thanks for your formula but it has to be in vba :)

@Peter_SSs well I dont really hav other characters but it is more like a Error Handler if a file is saved from someone else and this person uses those kind of characters.. not very likely but still..
Would need to check what characters can be actually be used to save a file. Many thanks to you and yes I like your code! ..)

@Phuoc... Thank you also for your code I will try all of the above !!

Many thanks!
 
Upvote 0
You're welcome. You have a variety of approaches to choose from. :)

Could I suggest that you move your Excel version etc from your signature to your profile (click your user name at the top right of the forum & choose 'Account details') so that it shows at the left of your posts as that is where most helpers will now expect to see it.

1586741208130.png
 
Upvote 0
Thanks Peter!
Yes I did change it as you suggested sorry did not look.

Cheers )
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top