Number stored as text

[deduwa]

I have a table with a dynamic number of rows. The cells are formatted to Accounting in column E & F. However in column F, a handful of numbers appear as text and not as accounting numbers.

Is there a code that goes down column F, and where the cell is a text cell, then firstly convert this to a number, then secondly format it in the same way the immediate cell to the left (i.e. column E) is formatted.

So for example, if in a 200 row table, cells F100 and F199 are stored as text, then I want to convert these two cells as numbers, and then format F100 in the same way that cell E100 is formatted and format F199 the same way that cell E199 is formatted.

Thanks

 

[mikerickson]

The quickest way to fix this is to use TextToColumns on column F, with a delimiter that doesn't split anything off. When its done, it should convert all the numeral text to true numbers.

 

[deduwa]

Thank you

 

[steve case]

Mike

I have excel 2013

I've downloaded this page MONTHLY HISTORICAL STATE CLIMATE SUMMARIES
to find that some of the values in column B come through as text. I can't make
them into real numbers that I can sort. I've tried the text to column routine,
doesn't work. In my old version of Excel (which I dearly miss) all I had to do
was multiply the formatted/stored as text number by 1 and it worked. The new
Excel with the "Ribbon" won't do it and I'm stuck. I've manually done the conversion,
but this is going to come up again when I have hundreds of numbers to fix.

I've tried to paste the column into the Note Pad and re-copy and paste back to excel
hoping to strip the formatting off - didn't work. I've tried =TRIM() and multiply by
one - didn't work.

Background:

I copied the web page using [Ctrl A] [Ctrl C] open Sheet1 and [Ctrl V] then copy
and paste values to Sheet2. Remove the columns and rows I don't want and
found the problem on the first sort.

My hard drive died a while back, and my old pre "Ribbon" Excel wouldn't load
on Windows 10.

 

[Peter_SSs]

Looks like the problem is CHAR(160) in front of some of the numbers.

Formula solution:
=IFERROR(SUBSTITUTE(B2,CHAR(160),"")+0,"")


Manual solution
Put the formula =CHAR(160) in a vacant cell and copy it
Select column B and invoke Find/Replace, paste into the Find box, leave Replace empty, ensure "Match entire cell contents' is not checked in Options, Replace All


BTW, you can still use the multiply by 1 trick in later versions of Excel, though it is not relevant in this case as it won't deal with CHAR(160). What makes you think you can't use it in Excel 2013?

 

[steve case]

Looks like the problem is CHAR(160) in front of some of the numbers.

Formula solution:
=IFERROR(SUBSTITUTE(B2,CHAR(160),"")+0,"")


Manual solution
Put the formula =CHAR(160) in a vacant cell and copy it
Select column B and invoke Find/Replace, paste into the Find box, leave Replace empty, ensure "Match entire cell contents' is not checked in Options, Replace All


BTW, you can still use the multiply by 1 trick in later versions of Excel, though it is not relevant in this case as it won't deal with CHAR(160). What makes you think you can't use it in Excel 2013?

First of all, thank you very much for figuring it out.

I should have noticed that there was a suspicious looking space on the formula bar.
Well I did sort of that's why I tried =TRIM() but it looks like TRIM doesn't trim everything
that creates a space. Maybe Microsoft should fix that along with a few other things.

What makes you think you can't use it in Excel 2013?

Because my application of the "Duck Test" yielded the answer, "That's what it looks like."
Well nothing is perfect including the Duck Test. I am pleased to find out that the multiply
by one trick does work in Excel 2013.

Well this exchange has been very educational - Mr. Excel is the best free site on the web.
I'm retired now and don't make spread sheets for people any more, but when I did, Mr.
Excel was my goto source on how to make my macros and formulas etc. do what I wanted
them to do.

 

[Peter_SSs]

First of all, thank you very much for figuring it out.

You're welcome.

.. it looks like TRIM doesn't trim everything
that creates a space. Maybe Microsoft should fix that along with a few other things.

In my view there is nothing to fix in relation to this. TRIM was designed specifically for the 'standard space' character - CHAR(32) - & Microsoft has provided other ways to deal with CHAR(160) & a number of other 'unusual' characters. From the Microsoft Support pages regarding TRIM:

Important: The TRIM function was designed to trim the 7-bit ASCII space character (value 32) from text. In the Unicode character set, there is an additional space character called the nonbreaking space character that has a decimal value of 160. This character is commonly used in Web pages as the HTML entity,  . By itself, the TRIM function does not remove this nonbreaking space character. For an example of how to trim both space characters from text, see Top ten ways to clean your data.

 

[steve case]

Thanks for education (-:

Now that I know what to look for, the formula you provided works - but I have to remember how to generate it or look it up on my saved email or file somewhere. So I figured out that =right(,)*1 works and it's easy to figure out what the # in =right( ,#) should be. I use =right(), =left(), =Len() & =Trim() all the time. =IFERROR() not so much.

I mentioned that Microsoft could fix other things too. Top of my list is an option to disable the "Date Function" Besides me, I have seen other people struggle trying to unravel the chaos that the "Date Function" can produce.