Turn Text To Values With Paste Special Values

Many times you would have received data from your IT system which is formatted wrong!  Well a gazillion times if you work in a mid sized firm 🙂

When you try and sum the values you get a count rather than a sum, that is because Excel reads the data as text rather than a value.

Tip 1: You can press F2 in the cell to see why it is in text format.

Tip 2: You can also press CTRL+1 (which brings up the Format Cells dialogue box) to confirm that it is in text:

Tip 3: You can also use the ISTEXT function to confirm a cell`s format:

Now you can easily convert the text into values by using the Paste Special > Values > Multiply combination.  Here is how…

STEP 1: Enter the number 1 in an empty cell

STEP 2: Copy that cell

STEP 3: Select the data range, Right Click and select Paste Special

STEP 4: Select Values & Multiply and press OK

Your data will be transformed into values:

If you like this Excel tip, please share it

email

Pinterest

Facebook

Twitter

Linkedin