how to add any character in between string in access vba

Question

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

there are 2 replacement in your question. one is before first integer and second is the last decimal. Am I correct ? You have solved 1st replacement while you stuck with second ? — Mukul Varshney
– Mukul Varshney, Commented Jan 9, 2017 at 14:46
I am still not able to extract the date, and the number at the end of the string and rearrange the string for expected result... — vivek padelkar
– vivek padelkar, Commented Jan 10, 2017 at 5:42

Mukul Varshney · Accepted Answer · 2017-01-10 09:20:29Z

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

Collectives™ on Stack Overflow

how to add any character in between string in access vba

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related