+ Reply to Thread
Results 1 to 10 of 10

Replace "illegal database" characters with "_" in one go

  1. #1
    Registered User
    Join Date
    02-01-2013
    Location
    atherton, qld, australia
    MS-Off Ver
    Excel 2003
    Posts
    7

    Replace "illegal database" characters with "_" in one go

    Hi,

    I have a big list of the full path to files that we need to make into only alphanumeric with only "_" so that they "definitely" work in databases. So
    P:\2012\367_mapping\Input\02 Culverts (minor)\Travelling Dairy Creek Road & Mode Road\Other Information\44353 - Travelling Dairy Creek Road.pdf
    will become
    \02_culverts_minor\travelling_dairy_creek_road_mode_road\other_information\44353_02_travelling_dairy_creek_road.pdf

    etc.

    I can run a =SUBSTITUTE(C1,"&","_") for each illegal character but was wondering if I can simply give it a list of illegal characters (,),~,` etc to replace with

    or

    The easier option would be to define that each character should be in [a...z], [1..9] and if not these replace with '_'

    I would have hoped the CLEAN function does this but that is not it's purpose.

    Any ideas?
    Attached Files Attached Files
    Last edited by coreagc; 02-01-2013 at 01:14 AM. Reason: added example

  2. #2
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2013
    Posts
    4,337

    Re: Replace "illegal database" characters with "_" in one go

    how about something like this

    Formula: copy to clipboard
    Please Login or Register  to view this content.
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir

  3. #3
    Registered User
    Join Date
    02-01-2013
    Location
    atherton, qld, australia
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Replace "illegal database" characters with "_" in one go

    Thanks but I have to then repeat each illegal character twice. Can't it be from a vlookup or similar, so all characters specified in a volookup are replaced with "_" if found or ideally characters that are not in the set [a...z], [1-9] are replaced with '_'

    So all the following and more are not allowed

    `~!@#$%^&*()-+=\|]}[{;:'"?/.>,</*
    Last edited by coreagc; 02-01-2013 at 02:10 AM. Reason: added list of illegal characters

  4. #4
    Forum Expert
    Join Date
    11-28-2012
    Location
    Guatemala
    MS-Off Ver
    Excel 2010
    Posts
    2,394

    Re: Replace "illegal database" characters with "_" in one go

    try the macro, it is acting on the first 25 rows of the copy.
    Attached Files Attached Files

  5. #5
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    7,249

    Re: Replace "illegal database" characters with "_" in one go

    Try this Macro.
    It replaces all special chrs By "_" in the activesheet.

    Please Login or Register  to view this content.
    Last edited by kvsrinivasamurthy; 02-01-2013 at 04:11 AM.

  6. #6
    Registered User
    Join Date
    02-01-2013
    Location
    atherton, qld, australia
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Replace "illegal database" characters with "_" in one go

    Thanks KV. Is it possible to get this to work only on the selected cells or ideally use it like "=ReplaceSplChrs(b2:b49)"? best.

  7. #7
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    7,249

    Re: Replace "illegal database" characters with "_" in one go

    To Use it as UDF You have to select one cell as input and in the designated cell you can get output. I dont know how to use this for range of cells. Experts pl advice

  8. #8
    Forum Guru Kyle123's Avatar
    Join Date
    03-10-2010
    Location
    Leeds
    MS-Off Ver
    365 Win 11
    Posts
    7,213

    Re: Replace "illegal database" characters with "_" in one go

    What sort of database/field type are you using? databases are usually happy with any character except "'"

  9. #9
    Registered User
    Join Date
    02-01-2013
    Location
    atherton, qld, australia
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: Replace "illegal database" characters with "_" in one go

    Kyle -It's for a spatial dataset that will link to files based on the relative path. I have found that non-alphanumeric characters in the folder/filenames is usually ok but does cause issues and I want to reduce the risk.

    Kv - Thanks. How do you define the output cell. If I looked at some options for setting the range of cells but they didn't work. See http://www.mrexcel.com/forum/excel-q...ted-cells.html

  10. #10
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    7,249

    Re: Replace "illegal database" characters with "_" in one go

    Pl see the attached file with UDF in B column.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1