Geek Answers Handbook

How to remove ALL numbers from a cell using a function or regular expression?

I have cells that look like this: one per line:

Duffy, John: "Legacy: Civilization and the Jews - Fanfare and Amp. Chorale, Symphonic Dances + Orchestral Suite. Bernstein," Dance of the City "Episodes. Royal Phil./R.Williams

Lilien, Ignace 1897-1963: Songs, 1920-1935. Anja van Wijk, mezzo & France van Root, piano

Hindemith, Trauermusik. Purcella, “Fairy Suite Queen. Rossini, String Sonata 6. Petrov, Ballet“ Creation of the World ”Suite. Bartok, Romanian folk songs Sz 56. Tartini, Concert for flute in G wAMaiorov Leningrad eagle. In Ancient and Contemporary music / Serov

Bizet, Verdi, Massenet, Puccini: Arias from Carmen, Rigoletto, Werther, Manon Lesko, Tosca, Turandot + Songs of Lara, Di Capua and others. Peter Dvorsky, tenor V. Bratislava Orch./Lenard Also performs 'Carmen'. Explicit & Thinking Thais. Rec.Live, 10/87

Fantini, Rauch, C. Straus, Priuli, Bertali: "Festival Mass in the Imperial Court of Vienna, 1648 'Yorkhire Bach Choir & Baroque Soloists + Baroque Brass London / Seymour

Vinci, Leonardo 1690-1730: Arias from Poluramida Riconosute, Dido Abbandonata, La Caduta dei Decemviri, Lo Cecato Fauzo, La Festa de Bacco, Catone in Utica. Maria Angeles Peters Sop. wMCarraro holding

Glitch, Mozart, Beethoven, Weber, Verdi, Wagner, Ponchielli, Mascagni, Puccini: Arias from Alchester, Don Giovanni, Fidelio, Oberon, Ballo, Tristana, Walkure, Siegfried, Gotterdammering, Gioconda, Cavalleria, Tosca. Helen Wilbrunn. Rec. 1919-24

I would like to delete ALL numbers. How can I do this using a formula? or maybe just look and replace with a regular expression?

+4
source share
3 answers

How to do it with VBA ...

  • Open the Excel workbook and paste the text that you specified as follows: alt text

  • Let these rows be selected.

  • Press "ALT + F11" to open the Visual Basic Editor.

  • Go to the "Insert" menu and open the module.

  • Enter this function:

    Sub clear() s = Array("0", "1", "2", "3", "4", "5", "6", "7", "8", "9") For Each r In Selection v = r.Value For i = 0 To 9 v = Replace(v, s(i), "") Next r.Value = v Next End Sub

  • Press the green play button to execute the VBA script. alt text

  • Go back to the sheet and see the result (no more digits): alt text

+8
source

I ran into a similar problem, but took a different path, wanting to avoid using vba. The recursive use of replacement did the trick for me, as shown below:

Raw data (in A1: An, n = integer) ROE -1.00 P / E 0.07 -0.85 ROC-ROE 0.02 -0.03, etc.

= SUBSTITUTE (SUBSTITUTE (SUBSTITUTE (SUBSTITUTE (SUBSTITUTE (SUBSTITUTE (SUBSTITUTE (SUBSTITUTE (SUBSTITUTE (SUBSTITUTE (SUBSTITUTE (SUBSTITUTE (A1, "1", ")," 0 ","), "2", ")," 3 ",") , "4", ")," 5 ","), "6", ")," 7 ","), "8", ")," 9 "," ")

which can be expanded to get rid of the "-" and the decimal indicator "." as well as "+" (if necessary) and, finally, extra spaces using

= trim (spare (spare) substitue (Substitute (Substitute (Substitute (Substitute (Substitute (Substitute (Substitute (Substitute (Substitute (Substitute (A1, "1", ")," 0 ","), "2", " ), "3", ")," 4 ","), "5", ")," 6 ","), "7", ")," 8 "", ")," 9 "," ), "-", ")," + ","), ".", ""))

where lowercase represents the difficulties added in the current step.

This naturally displays the entire array, changing the reference to the entire array that needs to be analyzed (A1: An), and enter the formula as an array (i.e., using Ctl + Enter instead of Enter to complete the task).

Then I use len (B1) to check the results I want.

Not elegant, really. But it can be a useful exercise for teaching students to think and code on the fly.

+2
source

UPDATED: I accidentally sent a contains function instead of a replacement!

Finding and replacing with Regex works fine, but you can edit your text words (for example, commas that were there before numbers, etc.).

Here is the function. The pattern is simply = RegexReplace (cell, "\ d", "")

 Function RegexReplace(ByVal text As String, _ ByVal replace_what As String, _ ByVal replace_with As String) As String Dim RE As Object Set RE = CreateObject("vbscript.regexp") RE.Pattern = replace_what RE.Global = True RegexReplace = RE.Replace(text, replace_with) End Function
+1
source

More articles:


All Articles