New Contributor

# 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

# Re: Convert Text to specific numbers

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

# Re: Convert Text to specific numbers

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.

# Re: Convert Text to specific numbers

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.

# Betreff: Convert Text to specific numbers

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)

# Re: Convert Text to specific numbers

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