Change Characters

silentwolf

Well-known Member
Joined
May 14, 2008
Messages
1,164
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
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,103
Office Version
  1. 2013
Platform
  1. Windows
@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.
 

silentwolf

Well-known Member
Joined
May 14, 2008
Messages
1,164
Office Version
  1. 2016
Hi,
fantastic :)
Thank you very much ! New that someone is outthere who knows to help ;)

Many thanks
Nice greetings
 

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
3,206
Office Version
  1. 2016
Platform
  1. Windows
Will the character "-" sometimes appear after the last "." ?
If not, maybe a worksheet formula could be used ? : =SUBSTITUTE(A1,"-","_")
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,365
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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)
 

Phuoc

Active Member
Joined
Apr 29, 2016
Messages
310
Or try:
Code:
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
 

silentwolf

Well-known Member
Joined
May 14, 2008
Messages
1,164
Office Version
  1. 2016

ADVERTISEMENT

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!
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
48,365
Office Version
  1. 365
Platform
  1. Windows
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
 

silentwolf

Well-known Member
Joined
May 14, 2008
Messages
1,164
Office Version
  1. 2016
Thanks Peter!
Yes I did change it as you suggested sorry did not look.

Cheers )
 

Watch MrExcel Video

Forum statistics

Threads
1,126,998
Messages
5,622,097
Members
415,876
Latest member
csibonga2k17

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
Top