Forum Discussion
Convert Text to specific numbers
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?
5 Replies
- 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 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)
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.
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.
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
