How to remove ALL numbers from a cell with a function or regex?_问答_开发者

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

Duffy,John: 'Heritage: Civilization and the Jews'- Fanfare & Chorale,Symphonic Dances + Orchestral Suite. Bernstein,'On the Town' Dance Episodes. Royal Phil./R.Williams

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

Hindemith,Trauermusik. Purcell,'Fairy Queen' Suite. Rossini,String Sonata 6. Petrov,'Creation of the World' Ballet Suite. Bartok,Romanian Folkdances Sz 56. Tartini,Flute Concerto in G w.A.Maiorov Leningrad Orch.for Ancient & Modern Music/ Serov

Bizet,Verdi,Massenet,Puccini: Arias from Carmen,Rigoletto,Werther,Manon Lescaut,Tosca,Turandot + Songs by Lara,Di Capua et al. Peter Dvorsky,tenor w.Bratislava Orch./Lenard Also performing 'Carmen' Overt.& 'Thais' Medit开发者_StackOverflowation. Rec.Live,10/87

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

Vinci,Leonardo1690-1730: Arias from Semiramide Riconosciuta,Didone Abbandonata,La Caduta dei Decemviri,Lo Cecato Fauzo,La Festa de Bacco,Catone in Utica. Maria Angeles Peters sop. w.M.Carraro conducting

Gluck,Mozart,Beethoven,Weber,Verdi,Wagner,Ponchielli,Mascagni,Puccini: Arias from Alceste,Don Giovanni,Fidelio,Oberon,Ballo,Tristan,Walkure,Siegfried,Gotterdammerung,Gioconda,Cavalleria,Tosca. Helene Wildbrunn. Rec.1919-24

I would like to remove ALL numbers. how do I do this with a formula? or maybe just search and replace with regex?

How to do that using VBA...

Open an Excel workbook and paste the text you provided this way:
Let those rows selected.
Press “ALT+F11” to open the Visual Basic Editor.
Go to the Insert Menu and open a Module.

Type in 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

Click the green Play button to execute the VBA script.
Get back to the sheet and see the result (no more digits):

I was faced with a similar problem, but went another way, wanting to avoid using vba. A recursive use of substitute did the trick for me, as illustrated below:

Original 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(A1,"1",""),"0",""),"2",""),"3",""),"4",""),"5",""),"6",""),"7",""),"8",""),"9","")

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

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

where the lowercase represents complications added in the current step.

This maps naturally onto the entire array by changing the range reference to the entire array to be parsed (A1:An) and entering the formula as an array (i.e., using Ctl+Enter instead of Enter to complete the task).

I then use len(B1) to check the results are what I want.

Not elegant, admittedly. But may be a useful exercise for teaching students to think and code "on the fly."

UPDATED: I had accidentally posted a contains function instead of replace!

Search and replace with Regex works fine, but you might want to edit your text afterwords (like commas that were there before the numbers, etc.).

Here is the function. 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