Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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' Meditation. 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?

like image 265
Alex Gordon Avatar asked Dec 09 '22 14:12

Alex Gordon


1 Answers

How to do that using VBA...

  1. Open an Excel workbook and paste the text you provided this way: alt text

  2. Let those rows selected.

  3. Press “ALT+F11” to open the Visual Basic Editor.

  4. Go to the Insert Menu and open a Module.

  5. 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
    
  6. Click the green Play button to execute the VBA script. alt text

  7. Get back to the sheet and see the result (no more digits): alt text

like image 188
Leniel Maccaferri Avatar answered Jan 17 '23 01:01

Leniel Maccaferri