Collection Management Services

Remove punctuation, spaces

For Copy and paste:

=LOWER(TRIM(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(B2,"("," "),")"," "),"-"," "),":"," "),";"," "),"!"," "),","," "),"?"," "),"["," "),"]"," "),"."," ")))

For reading and editing:

=
StripAccent(

LOWER(
TRIM(
SUBSTITUTE(

SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(
SUBSTITUTE(

SUBSTITUTE(

SUBSTITUTE(

SUBSTITUTE(
A1,
"("," "),
")"," "),
"-"," "),
":"," "),
";"," "),
"!"," "),
","," "),

"?"," "),

"["," "),

"/"," "),

"]"," "),
"."," "))))

Use on Alma Title and Publication Place

Strip Diacritics

Use this to replace diacritics with non-diacritic character

View Code > Insert > Module > copy and paste:

Alt+Q to close VBA

To use, enter formula =StripAccent(A2)

Count Unique Text

=COUNTA(UNIQUE(A2:A10))

To remove "The " or  "a " at the beginning of a title

first make a helper column to identify which ones have the word you want to remove

Then filter and remove the number of characters you want

Then filter and remove the number of characters you want

Use PowerQuery to remove Special Characters

To keep spaces while removing other special characters using Power Query in Excel, you can modify the Text.Select function to include spaces. Here's how you can do it:

1. Select your data range.
2. Go to the Data tab and select From Table/Range.
3. In the Power Query Editor, select Add Column > Custom Column.
4. Enter the following formula to keep only alphanumeric characters and spaces:

   Text.Select([ColumnName], {"A".."Z", "a".."z", "0".."9", " "})
   

5. Click OK.
6. Click Close & Load to return the cleaned data to Excel.

This formula will ensure that spaces are retained while removing other special characters