Convert Text to specific numbers

%3CLINGO-SUB%20id%3D%22lingo-sub-2152414%22%20slang%3D%22en-US%22%3EConvert%20Text%20to%20specific%20numbers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2152414%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20the%20data%20below%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Gervie_0-1613854903225.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F256344iDE36464F400D7C6F%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Gervie_0-1613854903225.png%22%20alt%3D%22Gervie_0-1613854903225.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20an%20attendance%20record.%20The%20data%20in%20the%20cells%20need%20to%20be%20converted%20into%20certain%20numbers%20as%20shown%20below%3A%3CBR%20%2F%3E%3CSTRONG%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20P%20%3D%201.5%3C%2FSTRONG%3E%3CBR%20%2F%3E%3CSTRONG%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20L%20%3D%201.0%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20E%20%3D%200.5%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%3CSTRONG%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20U%20%3D%200%3C%2FSTRONG%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20do%20we%20convert%20those%20texts%20into%20numbers%20using%20the%20equivalent%20amount%20above%3F%3C%2FP%3E%3CP%3EIs%20it%20doable%20even%20without%20using%20VB%20or%20macros%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2152414%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2152441%22%20slang%3D%22en-US%22%3ERe%3A%20Convert%20Text%20to%20specific%20numbers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2152441%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F961340%22%20target%3D%22_blank%22%3E%40Gervie%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20going%20to%20assume%20you%20just%20need%20to%20do%20this%20once%20and%20from%20here%20on%20out%20will%20enter%20the%20numbers%20that%20you%20want%2C%20although%20the%20method%20I'm%20going%20to%20tell%20you%20about%20will%20continue%20to%20work.%20But%20why%20enter%20P%20when%20you%20mean%201.5%20in%20the%20first%20place%2C%20right%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EUsing%20the%20main%20menu%2C%20click%20on%20Edit%3C%2FP%3E%3CP%3Eand%20then%20down%20to%20Replace%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22mathetes_0-1613856148768.png%22%20style%3D%22width%3A%20573px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F256350i951E50CB4427DB8C%2Fimage-dimensions%2F573x534%3Fv%3D1.0%22%20width%3D%22573%22%20height%3D%22534%22%20role%3D%22button%22%20title%3D%22mathetes_0-1613856148768.png%22%20alt%3D%22mathetes_0-1613856148768.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAnd%20then%20in%20the%20Dialog%20box%20that%20opens%20up%20fill%20it%20out%20as%20shown%20here%20and%20click%20on%20%22Replace%20All%22%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22mathetes_1-1613856234015.png%22%20style%3D%22width%3A%20607px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F256351iCB216F375DC39473%2Fimage-dimensions%2F607x413%3Fv%3D1.0%22%20width%3D%22607%22%20height%3D%22413%22%20role%3D%22button%22%20title%3D%22mathetes_1-1613856234015.png%22%20alt%3D%22mathetes_1-1613856234015.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFollow%20the%20same%20routine%20for%20replacing%20the%20other%20letters%20with%20the%20desired%20values%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2152445%22%20slang%3D%22en-US%22%3ERe%3A%20Convert%20Text%20to%20specific%20numbers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2152445%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F961340%22%20target%3D%22_blank%22%3E%40Gervie%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ESelect%20the%20range%20with%20letters.%3C%2FP%3E%0A%3CP%3EFormat%20the%20range%20as%20Number%20with%201%20decimal%20place.%3C%2FP%3E%0A%3CP%3EPress%20%3CSTRONG%3ECtrl%2BH%3C%2FSTRONG%3E%20to%20activate%20the%20Replace%20dialog.%3C%2FP%3E%0A%3CP%3EEnter%20P%20in%20the%20%3CSTRONG%3EFind%20what%3C%2FSTRONG%3E%20box%20and%201.5%20in%20the%20%3CSTRONG%3EReplace%20with%3C%2FSTRONG%3E%20box.%3C%2FP%3E%0A%3CP%3EClick%20%3CSTRONG%3EReplace%20All%3C%2FSTRONG%3E.%3C%2FP%3E%0A%3CP%3EEnter%20L%20in%20the%20%3CSTRONG%3EFind%20what%3C%2FSTRONG%3E%20box%20and%201%20in%20the%20%3CSTRONG%3EReplace%20with%3C%2FSTRONG%3E%20box.%3C%2FP%3E%0A%3CP%3EClick%20%3CSTRONG%3EReplace%20All%3C%2FSTRONG%3E.%3C%2FP%3E%0A%3CP%3EEnter%20E%20in%20the%20%3CSTRONG%3EFind%20what%3C%2FSTRONG%3E%20box%20and%200.5%20in%20the%20%3CSTRONG%3EReplace%20with%3C%2FSTRONG%3E%20box.%3C%2FP%3E%0A%3CP%3EClick%20%3CSTRONG%3EReplace%20All%3C%2FSTRONG%3E.%3C%2FP%3E%0A%3CP%3EEnter%20U%20in%20the%20%3CSTRONG%3EFind%20what%3C%2FSTRONG%3E%20box%20and%200%20in%20the%20%3CSTRONG%3EReplace%20with%3C%2FSTRONG%3E%20box.%3C%2FP%3E%0A%3CP%3EClick%20%3CSTRONG%3EReplace%20All%3C%2FSTRONG%3E.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2152502%22%20slang%3D%22en-US%22%3ERe%3A%20Convert%20Text%20to%20specific%20numbers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2152502%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F961340%22%20target%3D%22_blank%22%3E%40Gervie%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20a%20comment%2C%20if%20conversion%20is%20needed%20to%20do%20some%20stats%20(sum%2C%20average%2C%20etc%20attendance)%2C%20I'd%20do%20that%20within%20such%20stats%20calculations.%20Report%20with%20letters%20looks%20more%20human%20friendly.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2153333%22%20slang%3D%22de-DE%22%3ESubject%3A%20Convert%20Text%20to%20specific%20numbers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2153333%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F961340%22%20target%3D%22_blank%22%3E%40Gervie%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3EWith%20everyone's%20permission%2C%20here%20is%20a%20simple%20example%20that%20you%20can%20develop%20yourself.%3C%2FSPAN%3E%3C%2FSPAN%3E%20%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22VIiyi%22%3E%3CSPAN%20class%3D%22JLqJ4b%20ChMk0b%22%3E%3CSPAN%3EExample%20in%20the%20file.%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20be%20happy%20to%20know%20if%20I%20could%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20wish%20you%20continued%20success%20with%20Excel%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2153621%22%20slang%3D%22en-US%22%3ERe%3A%20Convert%20Text%20to%20specific%20numbers%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2153621%22%20slang%3D%22en-US%22%3EGo%20through%20the%20find%20and%20replace%20function%3CBR%20%2F%3EFirst%20select%20the%20data%20where%20you%20want%20to%20replace%20text%20with%20numbers%20.after%20that%20press%20ctrl%2Bh%3CBR%20%2F%3EEnter%20the%20find%20what%20as%20%22p%22%20or%20which%20text%20you%20want%20replace%20and%20enter%20value%20in%20replace%20with(%20ex%201.5)%20and%20select%20replace%20all%3C%2FLINGO-BODY%3E
New Contributor

I have the data below:

                                               Gervie_0-1613854903225.png

 

This is an attendance record. The data in the cells need to be converted into certain numbers as shown below:
                    P = 1.5
                    L = 1.0

                    E = 0.5

                    U = 0

 

How do we convert those texts into numbers using the equivalent amount above?

Is it doable even without using VB or macros? 

 

 

 

5 Replies

@Gervie 

 

I'm going to assume you just need to do this once and from here on out will enter the numbers that you want, although the method I'm going to tell you about will continue to work. But why enter P when you mean 1.5 in the first place, right?

 

Using the main menu, click on Edit

and then down to Replace

mathetes_0-1613856148768.png

 

And then in the Dialog box that opens up fill it out as shown here and click on "Replace All"

mathetes_1-1613856234015.png

 

Follow the same routine for replacing the other letters with the desired values

 

@Gervie 

Select the range with letters.

Format the range as Number with 1 decimal place.

Press Ctrl+H to activate the Replace dialog.

Enter P in the Find what box and 1.5 in the Replace with box.

Click Replace All.

Enter L in the Find what box and 1 in the Replace with box.

Click Replace All.

Enter E in the Find what box and 0.5 in the Replace with box.

Click Replace All.

Enter U in the Find what box and 0 in the Replace with box.

Click Replace All.

@Gervie 

As a comment, if conversion is needed to do some stats (sum, average, etc attendance), I'd do that within such stats calculations. Report with letters looks more human friendly.

@Gervie 

 

With everyone's permission, here is a simple example that you can develop yourself.

Example in the file.

 

I would be happy to know if I could help.

 

I wish you continued success with Excel

 

Nikolino

I know I don't know anything (Socrates)

Go through the find and replace function
First select the data where you want to replace text with numbers .after that press ctrl+h
Enter the find what as "p" or which text you want replace and enter value in replace with( ex 1.5) and select replace all