CODEWITHSUNDEEP
vbams-access
vivek padelkar
vivek padelkar

Reputation: 313

how to add any character in between string in access vba

i have string like "NIFTY29-12-2016CE6300.00" and i want output as : "NIFTY_29-12-2016_6300_CE"

the problem is first part i.e.(NIFTY) is not fixed in length it can be abcd,rdftghe or anything And last part i.e.(6300.00) is also not fixed length it can be 123.8888888.23.88989 or anything

try this code to get position of the first digit in a string and i able to concat "_" before that the code is as follow :

  If InStr(CStr(rs.Fields!Symbol), "CE") Then
                    StrOg = CStr(rs.Fields!Symbol)

                    For i = 1 To Len(StrOg)

                        currentCharacter = Mid(StrOg, i, 1)
                        If IsNumeric(currentCharacter) = True Then
                            GetPosChar = i
                            Exit For
                        End If
                    Next i
                    strtemp = Left(StrOg, GetPosChar) & "_" & Right() & "_"
                Else

i'm acheving till this :"NIFTY_" please help me!!!! thanks in advance

Upvotes: 0

Views: 303

Answers (1)

Mukul Varshney
Mukul Varshney

Reputation: 3141

Try below. Since you have not given proper explanation of where changes has to be made. I wrote the code with some assumptions like, symbol CE is available to you, we need to truncate all decimal part, etc. You can see teh code and proceed further.

Private Sub test()
  Dim StrOg As String

  StrOg = "NIFTY29-12-2016CE6123.8888888"
  Debug.Print "Org=" & StrOg
  Debug.Print "New=" & ReFormat(StrOg)

End Sub

Private Function ReFormat(ByVal inputText) As String
  Dim strNew As String
  Dim charPos As Integer
  Dim counter As Integer
  Dim found As Boolean

  'Search for 1st character from reverse and remove the 2 charters (i.e. symbol CE)
  found = False
  For i = Len(inputText) To 1 Step -1
     If (Not IsNumeric(Mid$(inputText, i, 1))) And Mid$(inputText, i, 1) <> "." Then
         charPos = i
         found = True
         Exit For
     End If
  Next i
  If found Then
    strNew = Left$(inputText, charPos - 2) & "_" & Right$(inputText, Len(inputText) - charPos)
  Else
    strNew = inputText
  End If

  'Search for 1st digit and if found update the string
  found = False
  For i = 1 To Len(strNew)
     If IsNumeric(Mid$(strNew, i, 1)) Then
         charPos = i
         found = True
         Exit For
     End If
  Next i
  If found Then
    strNew = Left$(strNew, charPos - 1) & "_" & Right$(strNew, Len(strNew) - charPos + 1)
  End If

  'Search for last decimal and truncate thereAfter
  charPos = InStrRev(strNew, ".")
  If charPos > 0 Then
    strNew = Left$(strNew, charPos - 1) & "_CE"
  End If

  ReFormat = strNew

End Function

Upvotes: 1

Related Questions