Results 1 to 3 of 3

Create a script that replaces all . with , and adds a zero before number in column

  1. #1
    FoolzRailer is offline Advanced Beginner
    Windows 8 Access 2013
    Join Date
    Nov 2015
    Posts
    45

    Create a script that replaces all . with , and adds a zero before number in column

    Hello

    I have a lot of data in columns that vary a fair bit in how they are named. I need to make make all the data so it has the same format.

    That means that I have to replace some text in the middle of a string. I've added a picture as an example as well.

    I could do a search and replace like approach with the . and , - But adding the 0 before that last number seems way harder, and it is also only before this last one the zero is needed, not anywhere else.
    Knudenavn
    D16100R-D16160R-27.0-2
    D16100R-D16160R-27.0-2-T

    Knudenavn
    D16100R-D16160R-27,0-02
    D16100R-D16160R-27,0-02-T


    Any help on an approach or suggestions would be much appreciated.



    Click image for larger version. 

Name:	Access.PNG 
Views:	21 
Size:	27.9 KB 
ID:	36001

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    13,060
    ?? I suggest you identify exactly what you want to change/replace.
    I see no mention of the rightmost "T" for example.

    For each string
    You want/need all "." to be changed to ","
    If the length of the data after the rightmost "-" is 1, then insert a "0" before that character , then concatenate "-T" to the right end of the string..

  3. #3
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    383
    I worked up a function that will process a string as you've requested based on the examples given. I can't know everything about your situation and dataset so it's obviously untested, but it could give you an idea of an approach you could take:

    Code:
    Public Function FixMyString(ByVal InputStr As String) As Variant
        Dim pos As Integer 'index position of the last digit(s) in the input string
        Dim InputLen As Integer 'num of chars in the input string
        Dim asciiCode As Integer 'ascii value of the last char in the string to see if it has a T (or anything other than a number)
        
        'Replace ALL period chars with a comma
        InputStr = Replace(InputStr, ".", ",")
        
        
        'Decide where in the sting to look to check if the last digits are 1 digit or two digits
        asciiCode = Right(InputStr, 1)
        InputLen = Len(InputStr)
        If Not (asciiCode > 47 And asciiCode < 58) Then 'If the last char in the string is anything other than a number
            pos = InputLen - 3
        Else
            pos = InputLen - 1
        End If
        
        'If there is hyphen in this position then insert a 0 char because we expected a digit
        If Mid(InputStr, pos, 1) = "-" Then
            InputStr = Left(InputStr, pos) & "0" & Right(InputStr, InputLen - pos)
        End If
        
        FixMyString = InputStr
    
    End Function

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 08-06-2018, 11:24 AM
  2. Replies: 10
    Last Post: 12-29-2017, 03:06 PM
  3. Replies: 9
    Last Post: 03-02-2015, 05:05 PM
  4. Need to create a script to auto update a field
    By storyiii.5 in forum Programming
    Replies: 1
    Last Post: 08-07-2013, 03:43 PM
  5. Replies: 1
    Last Post: 03-02-2010, 09:51 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums