Feb 20 2021 01:10 PM
I have the data below:
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?
Feb 20 2021 01:24 PM
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
And then in the Dialog box that opens up fill it out as shown here and click on "Replace All"
Follow the same routine for replacing the other letters with the desired values
Feb 20 2021 01:24 PM
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.
Feb 20 2021 02:20 PM
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.
Feb 21 2021 04:23 AM
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)
Feb 21 2021 09:00 AM