You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
Today's blog post on repairing numbers that are formatted as text is brought to you by Gary Willoughby, who writes Help content for Microsoft Excel.
A few months ago, my new manager e-mailed us (that is, her team of Excel writers). She had a spreadsheet with some columns of numbers that she wanted to total, and the answer was resolving to 0. When you have lots of values in a column and their total is 0, you have a problem: Those numbers may not be numbers at all! (cue the scary music).
. I love a challenge. She needed an answer, and fast. Turn me loose!After a little inspection by using the LEN function to determine the length of some random cells (I don’t very often trust imported data!), I noticed that there were leading and trailing spaces and possibly non-printing characters in the cells. Because they all seemed to be between 10000 and 99999 and included commas, I knew every cell should contain 6 characters. LEN showed that many contained more than 6.
.Apparently this data had been sent around in e-mail messages, maybe pasted into and copied back out of Word, batted around in who knows what other programs, and finally (after plenty of mayhem had been applied) pasted into a spreadsheet.
The first thing I did was use the CLEAN function nested inside the TRIM function, like this:=TRIM(CLEAN(A2))
I put this formula in B2, and then dragged it down the B column for every row that contained a value in column A. The CLEAN function removed any non-printing characters, and TRIM finished up by stripping any remaining leading and trailing spaces.
Next, I selected the cells in column B that contained the formula and copied and pasted them onto themselves with the Paste Values command to convert the formulas to actual values. Then, I moved the original values out of column A and replaced them with the cleaned and trimmed values from column B. I saw a green indicator in the upper-left corner of each cell telling me these values appeared to be numbers formatted as text. Almost home!
Making sure all the cells containing values in column A were selected, I clicked the green indicator in cell A2 and then chose the Convert to Number option. Because I selected all the cells before I clicked Convert to Number, Excel acted on all selected cells that had that issue.
.There…now I'm able to quickly add those numbers and get a real answer!
--Gary Willoughby
Comments: (17) Collapse
There an "old school" way to do this as well.
Enter 1 in B1. Copy B1,
Select A1:10
Paste Special, Multiply
Excel will automatically convert the #'s to #s.
Delete B1
partial RSS feeds? really?
Another way: Select the range, click "Text to Columns", choose "General" in "Column Data Format" and you're done.
For one time converts I use the same approach as @jg :Text to columns.
To change these numbers back to text (when you need to retain leading zeroes on part numbers, etc.), copy A1:10 and paste into Notepad. Format A1:10 to text. copy numbers from Notepad and Paste Values back into A1:10.
The TRIM(CLEAN(...)) approach is useful, but fails to handle the non-breaking space, ANSI 160. That one commonly pops up when copying in data from other apps, especially web pages.
Thus:
=TRIM(CLEAN(SUBSTITUTE(A2,CHAR(160),"")))
Many great alternatives and tips -- great discussion, folks!
RobertSeattle -- I use your method, but slightly different and I would say faster. Why bother typing 1 and multiplying, when you can just copy a blank cell, paste special and add? No need for clean up either by deleting the 1 you typed.
PatrickMatthews -- Great point about the non-breaking space. TRIM should really take care of it since it's a type of space and not a non-printing character. Would you call that an bug in XL?
I always use the RobertSeattle's method. Multiplying all numberlike text cells by 1 using Paste Special does the trick.
Hi Folks
My problem is different. Whenever I paste a data (a number basicaly) in a cell in XL, the pasted data gets converted to date.
Can I have help on this.
Sanjay, this can happen for a number of reasons. A likely one is that your destination cell might already be formatted as a date. If you paste a number in a cell from a program other than Excel, the destination format (the cell's format) will be respected. Note that if you paste a number *from Excel* into a cell formatted as a date or as text, Excel overrides that cell's format and pastes it in as a number and changes its format to General (unless you explicitly choose a specific Paste option).
Can you provide some more details about your destination cell and your source data?
=Value(Trim(Clean(A2))) is a great combo to remember as well!
@Sanjay try Ctrl+Shift+! to see if the dates convert back to the numbers you want, if this works it means that the cells have been formatted as date.
Why don't you just directly click Convert to Number ? It works to the same result.
Sorry to intrude - but my prob is I want to clear all those horrid little green arrows [even tho its March 17th]
I use Excel to present diagrams and use Nos only as text - with no calcs needed.
How can I cut the Irish triangular intruders ?
Terry
Comments: (loading) Collapse