Results 1 to 12 of 12
  1. #1
    DaveT99 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    76

    QUERT Replace Character in a specific position with a new character & extract the last character

    I have downloaded some data from the web. The original data was in different columns but was downloaded to a single column


    I am trying to split the data into specific data fields
    I have extracted the first column using the left function based on the 1st space and the right function

    This is one example of the remaining data 0147 1322 03 HEX. HEAD SCREW 4 after I have extracted a drawing referance number

    a) I would like to insert 2 dashes to make 0147-1322-03, but I can always do it in stages e, g replace the 5th character with a "-" and then repeat for the 2nd "-"

    b) what would be the expression to extract the last digit

    Many regards for you help, hope the above is clear

    Dave

  2. #2
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Office 365
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    a)
    Code:
    Replace(strText, " ", "-")
    For example, Replace("0147 1322 03","," ", "-") gives 0147-1322-03

    b) Do you need the last digit (4) separately from the above? e.g. Right(strText,1)
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  3. #3
    DaveT99 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    76
    Thanks for the first reply, greatly appreciated

    from the example 0147 1322 03 HEX. HEAD SCREW 4 i would like to extract the 4 in a separate field

  4. #4
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,859
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  5. #5
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Office 365
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by DaveT99 View Post
    Thanks for the first reply, greatly appreciated

    from the example 0147 1322 03 HEX. HEAD SCREW 4 i would like to extract the 4 in a separate field
    If the final character is always an integer, you can use the expression I provided:
    Code:
    FieldName= Right(strText,1)
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  6. #6
    DaveT99 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    76
    Hi Isladogs
    Perhaps I have misunderstood
    I typed in Mat-Desc=right(strText,1) and also EXP:Mat-Desc=right(strText,1)
    and neither worked
    Sorry to bother to be a pain

    Cheers
    dave

  7. #7
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    4,859
    Quote Originally Posted by DaveT99 View Post
    Hi Isladogs
    Perhaps I have misunderstood
    I typed in Mat-Desc=right(strText,1) and also EXP:Mat-Desc=right(strText,1)
    and neither worked
    Sorry to bother to be a pain

    Cheers
    dave
    Really?, works fine for me?
    Code:
    SELECT Transactions.Description, Left([description],4) AS Expr1, Right([description],1) AS Expr2
    FROM Transactions
    WHERE (((Left([description],4))="0147"));
    Edit: You appear to be confusing VBA with SQL?
    Attached Thumbnails Attached Thumbnails right.PNG  
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba

  8. #8
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Office 365
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    strText was a variable meant to represent your string value as shown in post #1
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  9. #9
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    just wondering if the example is too basic - screws come in different sizes including 1/2mm - 3.5, 4.5. 5.5 etc

  10. #10
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Quote Originally Posted by CJ_London View Post
    just wondering if the example is too basic - screws come in different sizes including 1/2mm - 3.5, 4.5. 5.5 etc
    Ditto. I was thinking, if really a screw, then upper sizes could be 10 or 12 at least, and that's 2 digits. Worse if 4 represents a quantity and not a size.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  11. #11
    DaveT99 is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    76

    Replace Character in Specific Position

    Thanks for all input, I may have confused you all by listing a single record from the Atlas Copco Catalogue download from the WEB.
    Re the comments on screw sizes, that record doesnt give a size only 2 are installed on the Machine. I am not too worried about the descriptions it is the Atlas Copco P/Ns that are the key.
    I have now extracted the last character, also used the 1st SQL given to insert the SPACE, however that inserts a dash in all spaces and not just in the Atlas Copco P/N
    The column PASTE was the original download from the WEB, which starts with the Exploded Parts diagram Ref No

    A big thanks for everybodys input
    dave
    Attached Files Attached Files

  12. #12
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Don't have time to download this morning but can say, wrt
    however that inserts a dash in all spaces and not just in the Atlas Copco P/N
    I think to fix that you need to id some sort of stability in a pattern.
    F'rinstance, the dot/period always defines the end of the pn: 0147 1322 03 HEX.
    Or there are always this many characters for the numeric part (including spaces): 0147 1322 03
    Or something else.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

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

Similar Threads

  1. Replies: 22
    Last Post: 10-30-2021, 02:41 PM
  2. Replies: 2
    Last Post: 09-05-2018, 11:56 PM
  3. Replies: 18
    Last Post: 10-11-2017, 03:07 PM
  4. Character Replacement by Position in String
    By J Bhujanga in forum Queries
    Replies: 5
    Last Post: 07-08-2017, 05:16 PM
  5. replace a character with a wildcard
    By neeedhelp in forum Programming
    Replies: 2
    Last Post: 04-11-2011, 05:02 PM

Posting Permissions

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