Convert text into number in Excel

%3CLINGO-SUB%20id%3D%22lingo-sub-2768227%22%20slang%3D%22en-US%22%3EConvert%20text%20into%20number%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2768227%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%26nbsp%3B%3CBR%20%2F%3EI%20was%20wondering%20if%20anyone%20was%20able%20to%20help.%20I've%20been%20racking%20my%20brains%20for%20ages%20and%20search%20countless%20forums%20to%20try%20and%20figure%20this%20out.%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EI%20have%20a%20Microsoft%20form%20that%20has%20a%20Likert%20scale%20question%20and%20Microsoft%20forms%20does%20not%20allow%20for%20a%20label%20and%20a%20numerical%20score%20to%20the%20label.%20So%2C%20when%20I%20export%20the%20responses%20from%20the%20form%20into%20excel%20the%20response%20label%20appears%20rather%20than%20a%20score.%20I%20want%20to%20convert%20the%20label%20to%20their%20score%2Fnumerical%20value%26nbsp%3B%20%26nbsp%3Bi.e.%20None%20of%20the%20time%20%3D%201%2C%20Rarely%20%3D%202%2C%20Some%20of%20the%20time%20%3D%203%2C%20Often%20%3D%204%2C%20All%20of%20the%20time%20%3D%205%20so%20I%20have%20numerical%20data%20to%20analyse.%3CBR%20%2F%3EIs%20there%20a%20way%20to%20convert%20these%20labels%2F%20text%20in%20excel%20to%20their%20score%20numerical%20value%20so%20the%20responses%20appear%20as%20their%20score%20in%20the%20sheet%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EI%20really%20appreciate%20any%20help.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20put%20a%20screen%20cap%20of%20my%20Microsoft%20from%20and%20its%20excel%20output%20for%20reference.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Beth97Salt_0-1632163934356.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F311626i7E73EE47BDB8C35C%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Beth97Salt_0-1632163934356.png%22%20alt%3D%22Beth97Salt_0-1632163934356.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Beth97Salt_1-1632163976047.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F311627iE6B8091DAECB205F%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Beth97Salt_1-1632163976047.png%22%20alt%3D%22Beth97Salt_1-1632163976047.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%2C%26nbsp%3B%3CBR%20%2F%3E%3CBR%20%2F%3EBethany%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2768227%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2768291%22%20slang%3D%22en-US%22%3ERe%3A%20Convert%20text%20into%20number%20in%20Excel%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2768291%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1161448%22%20target%3D%22_blank%22%3E%40Beth97Salt%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EUnfortunately%2C%20your%20screenshots%20are%20so%20small%20that%20I%20cannot%20read%20them.%3C%2FP%3E%0A%3CP%3EPress%20%3CSTRONG%3ECtrl%2BH%3C%2FSTRONG%3E%20to%20activate%20the%20Replace%20dialog.%3C%2FP%3E%0A%3CP%3EEnter%20%3CSTRONG%3ENone%20of%20the%20time%3C%2FSTRONG%3E%20in%20the%20%3CSTRONG%3EFind%20what%3C%2FSTRONG%3E%20box%20and%20%3CSTRONG%3E1%3C%2FSTRONG%3E%20in%20the%20%3CSTRONG%3EReplace%20with%3C%2FSTRONG%3E%20box%2C%20then%20click%20%3CSTRONG%3EReplace%20All%3C%2FSTRONG%3E.%3C%2FP%3E%0A%3CP%3ERepeat%20for%20the%20four%20other%20options.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi, 
I was wondering if anyone was able to help. I've been racking my brains for ages and search countless forums to try and figure this out. 

I have a Microsoft form that has a Likert scale question and Microsoft forms does not allow for a label and a numerical score to the label. So, when I export the responses from the form into excel the response label appears rather than a score. I want to convert the label to their score/numerical value   i.e. None of the time = 1, Rarely = 2, Some of the time = 3, Often = 4, All of the time = 5 so I have numerical data to analyse.
Is there a way to convert these labels/ text in excel to their score numerical value so the responses appear as their score in the sheet? 


I really appreciate any help. 

 

I've put a screen cap of my Microsoft from and its excel output for reference. 

 

Beth97Salt_0-1632163934356.png

Beth97Salt_1-1632163976047.png

 



Thanks, 

Bethany 

2 Replies

@Beth97Salt 

Unfortunately, your screenshots are so small that I cannot read them.

Press Ctrl+H to activate the Replace dialog.

Enter None of the time in the Find what box and 1 in the Replace with box, then click Replace All.

Repeat for the four other options.

@Hans Vogelaar to me you are a genius! Its worked. After hours of searching this has worked and its so simple. Thank you so much for your help.