Software

Quickly convert text values to numbers with Excel's Paste Special

If you regularly copy numerical data from Access tables to Excel spreadsheets, you may know that doing calculations on the copied text is challenging, if possible at all. Here's how to convert those values from their native text versions to more workable number fields in Excel.

After copying data from an Access table, you notice that you cannot perform any calculations on what you thought were number fields. Instead of numbers, the imported values behave as text. By using Excel's Paste Special feature, you can quickly convert those values to numbers. Follow these steps:

1. Click a blank cell formatted as a number cell and enter 1 in the cell.

2. Go to Edit | Copy.

3. Select the cells that contain the text values that you want to convert.

4. Go to Edit | Paste Special.

5. In the Operation section, click Multiply.

6. Click OK.

When importing data from some accounting programs, you may find that negative numbers import as text with the negative sign (-) to the right of the number. For example, suppose after importing, you find the text value 100- in cell H4. To convert that value to a negative number, enter this formula in a blank cell: =LEFT(H4,LEN(H4)-1)*-1.

Miss a tip?

Check out the Microsoft Excel archive, and catch up on our most recent Excel tips.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

27 comments
viren_lucky1
viren_lucky1

hi i want to convert numbers into text(money)without using vb script. is there any formula in excel?

syedqaj
syedqaj

How to convert momaric value into English word? just like I have (2500) so how to convert into English Word? Please tell me. My E-mail Address is syedqaj@yahoo.com & syedqaj110@yahoo.com & syedqaj@hotmail.com & syedqaj@gmail.com Please quick please define in detail (as like step by setp) Thanks

DBlayney
DBlayney

Good tip, I guess it would work just as well using "Add 0" which is what I would use in a formula. I have one observation to make: why COPY data from Access? If you use the "Office Links" option "Analyze it with Excel" (fortunately reduced to a simple Excel logo if placed in a tool bar) all the fields that are defined as numeric in Access come out as numeric in Excel. The same goes for dates.

collinsb
collinsb

This is an excellent tip MaryAnn - I have run into this issue before. Thanks for the quick and easy solution!

john.furstenwerth
john.furstenwerth

Paste Special-Multiply (by 1) serves to remove unwanted hyperlinks as well.

EdSykora
EdSykora

As with most applications there are usually more than one way to achieve the same result. Another option of converting text to numbers is to: 1.Highlight the range 2.Select Data on the menu bar 3.Select Text to Columns from the drop down menu 4.Choose delimitted or fixed width radio button then next 5.Next 6.Choose to format the numeric data as either general or date 7.Click finish The above steps may seem like a round about way of gettting the result, but doesn't take long and negative numbers should be formatted as such, and if there are any extra characters such as spaces they can be eliminated during the conversion process.

jefft
jefft

Why not just use =Value(A1)? Copy this formula down the column and then use Copy -> Paste Special -> Values?

mtrevino57
mtrevino57

This is a good one. I was using Excel Value function to convert a substring to a number, this is cleaner =Value(Mid(a1,5,5) becomes =Mid(a1,5,5)*1 THANKS!

RonElstun
RonElstun

I am confused as to why you would not just use the smart tag to convert text to numbers? Is thise for older versions of Excel? You can use smart tags to convert imported or selected cells to numbers - quicker than quick for me.

dhays
dhays

It may work on standard numbers, but it doesn't work on dates exported from Outlook's task feature to an Excel Spreadsheet. When a set of dates is operated on as suggested by this tip, the dates get converted to meaningless numbers, possibly the numeric value of the date, just doesn't look like a date, hence not what I wanted. For example--8/22/2007 becomes: 39316.00 which could be the number of days since the beginning of Excel time, to me it is meaningless. It would be easier to do a mass substitution to remove the appostrophe from th ebeginning of the field, but there seems to be no need as the data still acts like a number in that I can subtract two columns and come up with a number--the number of days between the two.

Architect
Architect

Another option is if after importing data you find that number cells are formatted as text you can highlight those cells (they should have a green triangle in the upper left corner of each cell, right click on the box with the exclamation point to the left of the cells highlighted and select 'Convert to Number'. The green triangles disappear and the numbers stored as text are now numbers stored as numbers.

coulibalyr
coulibalyr

Hi, I m agree with this tip. I try it and i think, it's genial. Thank you M. A. R.

inertman
inertman

once i was importing a very large access table of valuesand wonder if this could bedone on a whole batch. ie, i was importing inflation rates for each month in 10 year incrimants, i had to change each value manually to be able to perfore mathmatical functions on them. but i needed the years as well and didn't need to perform math on them. i may try it next time i'm doing anything like that and post results if no body else has by then.

steve
steve

All this time I've been going to 'Data' > 'Text to Columns', but you can only do this one column at a time. Stroke of genius!

eamonwall
eamonwall

Very useful. Simple, yet extremely effective tips.

cb.kaltelco
cb.kaltelco

This lady has given more clear tips than all the printed material I have seen yet(and that's lots)! Well done!!

linda.hasa
linda.hasa

Excel calculates the workbook when smart tag used. This is much slower than using Text To Columns. I haven't used this paste special method yet, but it is probably faster than the smart tag as well.

swexpert
swexpert

That number IS useful - Excel stores dates as numbers, with "Day 1" being jan 1, 1900 and every day since then adding 1 to the date number (just like Star Trek!!). After your Paste Special, select those cells and format as whatever kind of date you want. You should be OK. ALTERNATELY, operating on date-text cells: For dates that look like dates but act like text (this assumes all your dates are same format and width, such as mm/dd/yy): 1. Insert a blank column to the right of your text-dates (this is just my way of being safe and is optional). 2.Select the cells/column (yeah, only one column at a time - so too bad but...) 3. Select Data>>Text to Columns... 4. First step of the wizard, select Fixed Width and Next. 5. Second step of the wizard, click just to the right of the sample date shown, then Next. 6. Third step of the wizard, on the upper right you should have options for Column Data Format, and one of those should be Date. Select Date and then use the drop-down list to select your desired date format. Then type in the name of the first cell where the converted data should go (ie the cell coordinates of the first cell in your inserted blank column). Or point it out with your pointer. Next is no longer available because this is the last step of the wizard. Click Finish. Ta-da.

jpol99
jpol99

Yes, the 39316.00 is the numerical representation of the date. Format that cell as a date format you would like and it will appear as 8/22/2007.

a.southern
a.southern

Isn't there a way to automate this using the text/number properties of a cell? I've mainly been working with Outlook of late, but I can convert a Body of email from HTML to TEXT easily enough, I just think there should be a function in Excel that can be performed on the Cell to convert from String to Number/Integer. Of course, failing that, the Paste Special is a great function. My favourite is the transpose feature of it, where it takes a block of data in and pastes the columns as rows and vice versa. Great when turning one standard format into another standard format (the joys of bureaucracy!) -AS

TelcoChuck
TelcoChuck

I have had very good results by simply selecting a column of text formatted numbers and parsing using Data | Text to Columns?. Note that this only works correctly on one column at a time.

RichardIH
RichardIH

Click on the cell containing the text to convert to a number. The smart pop-up appears. Don't do anything with it yet - it will stay there. Extend the current selection over all cells that you want to change. Scroll back to the first selected cell, and select convert to number from the smart tag. Bingo - all text in the selection has been converted.

gwhittaker
gwhittaker

Excel has the INT() function, which returns the Integer represented by a text number. For floats, you could use POWER(A1,1).

bfoo
bfoo

Very clever. Thanks

Editor's Picks