finding full stops in google sheet cell strings, excluding file extensions eg .xlsx
Im attempting to migrate from Google Drive to Microsoft OneDrive and the migration continually fails due to invalid characters such as ~ " # % & * : < > ? / \ { | }.
I've removed all instances of the above characters from the files/folders and it is still failing.
I believe it is due to full stops in the middle of file/folder names an example is below:
Acuse.GASP8409257P9.6.2021.pdf
Basically I would want to find all instances of full stops in the middle of the file names, while excluding just files that have file extensions such as .docx
Would a regex do the job here? I've attempted to find one online, but to no avail, any help is appreciated.
I've attempted to use a regex to find the full stops but nothing is working as I would like.
UPDATE
(following OP's comment): "...how to replace the full stops with underscores _ instead"
=SUBSTITUTE(REGEXREPLACE(A2,"\D+$|[^a-zA-Z0-9\.]",""),".","_")
®EXEXTRACT(A2,"\D+$")
result: Acuse_GASP8409257P9_6_2021.pdf
Original reply
Please try the following
=REGEXREPLACE(A2,"(\W)|(.\D+$)","$2")
OR
=REGEXREPLACE(A2,"\D+$|\W","")
®EXEXTRACT(A2,"\D+$")
(Do adjust the formula according to your ranges and locale)
- Get all pipe-delimited words inside of all curly braced expressions in a string
- Using regex matched groups in pandas dataframe replace function
- Basic PCRE expression to match word anywhere in a line + another word always at the end of line
- string split using regex PHP
- Split string representing a comparison condition into its three parts
- using grep inside make to check if a file contains an export to PATH
- Grabbing a specific url from a webpage with re and requests
- QT - QInputDialog How to Validate?
- How to fill gap between two characters with regex
- Golang Regex extract text between 2 delimiters - including delimiters
- Regular expression loop makes duplicate matches due to similar words. How to avoid?
- HTML5 pattern for validating IP with mask
- Why does running the same regex twice yield different results?
- Regex to find double whitespace not on the start of a line
- Extract BBEdit search history into a shell script
- How to add http:// if it doesn't exist in the URL
- Regular expression to return text between parenthesis
- Find procedure name in all other procedures
- How to use posix regexp operator for substring match
- regex for checking URL's wildcard for both subdomain and top level domain?
- How to split a code looking like '10abc' in numbers then letters?
- Replace (~) with Double Quote (") in C#
- Steps to draw a DFA (or NFA) from a simple statement?
- Elegant regular expression to match all punctuations but not "'" in emacs Lisp?
- Matlab split string multiple delimiters
- Split on regex (more than a character, maybe variable width) and keep the separator like GNU awk
- How to use regexp to perform a glob in C/C++ (Linux)
- Regexp matching a string - positive lookahead
- Extract values from string using regex
- Does the Python regular expression module use BRE or ERE?