Problems converting text to general/number cells in excel 2013

**jofree** · 05-10-2015, 09:44 PM

Hi!, i´m using Excel 2013 on win 8.1, I pasted a table from a website into Excel, the numbers in it are formated in text since they are allingned to the left and no formula like sum work on them, I tried every posible method available to convert those cells into numbers, They don´t have any exclamation mark next to them so that I can´t chose to convert directly, I tried the special paste method by creating and copying a "1" cell, and then multiplying all cells to that number and nothing happens, the numbers keep being shown to the left and no formula work, I tried converting the cells into an Excel table but it doesn´t work, nothing happens. Some cells have a letter next to its number like 5p which I thought could be the cause but if I try selecting only the cells with numbers without letters nothing Works either. I don´t know what to do now, this should be a very easy thing to do but I hope not to have to enter all numbers manually in new cells, if anyone knows how to convert these apparently text cells to general or number cells please give me a clue, thanx!!

**FDibbins** · 05-10-2015, 09:57 PM

Hi, welcome to the forum

Check to see if there are leading/trailing spaces in the cells?

**Tony Valko** · 05-10-2015, 10:03 PM

Copying and pasting from the web is notorious for getting html "junk" included with the data.

The most common "junk" is a whitespace character called a non-breaking space. It's also known as char 160 nbsp.

The macro at this website will remove all leading/trailing
and multiple interspersed char 32 space characters.
It will also remove and/or convert char 160 non breaking
spaces into standard char 32 space characters. It will
work on text or numbers and the numbers will be
converted to true numeric numbers.

I use this macro dozens of times every single day!
It's a real time saver.

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

**jofree** · 05-10-2015, 10:05 PM

Hi!

In fact, I discovered there are spaces next to each number and once i delet that space they allignt to the right and formulas work, but should I do this with every cell?, is there a method to eliminate those spaces in one step for all cells?

**FDibbins** · 05-10-2015, 10:35 PM

Try using a helper column with =TRIM(a2) where A2 is the cell with the data in it

If that doesnt work, upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

**Tony Valko** · 05-10-2015, 10:39 PM

Originally Posted by jofree

is there a method to eliminate those spaces in one step for all cells?

Yes.

See post #3.

**jofree** · 05-10-2015, 11:14 PM

Thanks all!!, I used the macro and it partially solved my problem, while after applying the macro I was finally able to use the "special paste" method to convert all to numbers, some cells which also contain letters like: "5p" are still not recognized as numbers, the "p" only mean they´re provisinal numbers, is there a way to only use the numbers from those cells without removing the letter??

**FDibbins** · 05-11-2015, 12:32 AM

Perhaps you missed this comment in post # 5?

If that doesnt work, upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

That way, we wont be guessing

**FDibbins** · 05-11-2015, 12:32 AM

Perhaps you missed this comment in post # 5?

If that doesnt work, upload a small (clean) sample workbook (not a pic) of what you are working with, and what your expected outcome would look like.

That way, we wont be guessing

**jofree** · 05-11-2015, 01:03 AM

tablefrominternet.xlsm

Here it is, as you see it´s a simple table copied from internet, i run the "trimall" macro on it, but the cells with numbers and letters won´t perform as numbers. My expected result: I just want to be able to use formulas on the cells which contain numbers and letters too, while keeping the letters in them, is this posible or is it mandatory to trim all letters from the cells to do that?

**FDibbins** · 05-11-2015, 01:34 AM

Not sure which way you need to add the numbers, I assumed by row, try this...
=SUMPRODUCT(--(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A3:L3,"p",""),",","."),":",0),"e","")))
copied down

**jofree** · 05-11-2015, 02:05 AM

I replaced the commas in the formula with semicolons and changed the cells references with the ones i wanted and it worked very nice, the content of the cells still allign to the left though, thanks a lot!!

**FDibbins** · 05-11-2015, 02:22 AM

Im happy it worked for you

If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neat and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below my 1st post to you)

**FDibbins** · 05-11-2015, 02:22 AM

Im happy it worked for you

If this answered your question, please take a moment to mark the thread as "solved" - it helps keep things neat and tidy lol, and consider adding the reputation to those that helped (see points 2 & 3 below my 1st post to you)

Problems converting text to general/number cells in excel 2013

LinkBack

Thread Tools

Rate This Thread

Display

Problems converting text to general/number cells in excel 2013

Re: Problems converting text to general/number cells in excel 2013

Re: Problems converting text to general/number cells in excel 2013

Re: Problems converting text to general/number cells in excel 2013

Re: Problems converting text to general/number cells in excel 2013

Re: Problems converting text to general/number cells in excel 2013

Re: Problems converting text to general/number cells in excel 2013

Re: Problems converting text to general/number cells in excel 2013

Re: Problems converting text to general/number cells in excel 2013

Re: Problems converting text to general/number cells in excel 2013

Re: Problems converting text to general/number cells in excel 2013

Re: Problems converting text to general/number cells in excel 2013

Re: Problems converting text to general/number cells in excel 2013

Re: Problems converting text to general/number cells in excel 2013

Thread Information

Users Browsing this Thread

Similar Threads

converting general cells format to text

Converting time cell into general number

Converting general number to decimal number

[SOLVED] Converting 'General' formatted cells to Text formatted cell using.

Bookmarks

Bookmarks

Posting Permissions