Hi All,


I'm following the Covid numbers from virusncov.com and copying their data into my own sheet for analysis. One of the columns has figures like +43,852+0.5%


Unfortunately Excel doesn't recognize this and will not place a single figure in the cell. Is there away of changing the starting + to an = sign, thus making it a number.


Best Rgds,
Dave Robinson

By the way, I do not use any of the data gathered by virusncov.com for anything other than my own curiosity and interest.


Best Rgds,
Dave R.

How are you getting the data into excel? Power query or a simple copy and paste?

Attched i have done a power query version, using the second plus as a delimiter (couldnt get it to delimit on the line feed, but i am no expert) - this should be able to be refreshed daily when the new data is availible.

the Second sheet is the text to columns version - probably the most simple, just insert a column and text to columns

the third sheet is using formulas. this is possible but when i copied and pasted the data i got a few different formats, i dont know why but it would take a few ifs to get it right.

Attached Files

Cell formula error_Purfleet.xlsx (261.7 KB, 10 views)

Hi Dave,

I’m not exactly sure how you want the figures to display in columns – whether the two example figures you have given e.g. 43,853 and the 0.5% are required to be in separate columns or together in one column?
Anyway, going on the assumption first that its two separate columns you want to format, here goes:
1. Select all the cells or the entire column you want to enter the numbers into
2. Right-click on the selected cells and choose ‘format cells’
3. From the Format Cells dialogue box, on the Number tab, click ‘custom’
4. In the ‘type’ box, above the drop down list, clear it and enter: +_-*(hit the space bar once) #,##0
5. The plus sign is left aligned unfortunately, but putting it right next to the number knocks the thousand comma out of the correct place!
6. To make the column look tidier, you can always narrow the width which brings the + sign nearer to the number.

For the column containing the percentage number:
1. Again, select all the cells or the entire column you want to enter the numbers into
2. Right-click on the selected cells and choose ‘format cells’
3. From the Format Cells dialogue box, on the Number tab, click ‘custom’
4. In the ‘type’ box, this time enter: +_-*(hit the space bar once) 0.#%
5. The plus sign is again left aligned, but just narrow the column width to bring it closer to the number.

Therefore, when you enter just the numbers, the plus sign will automatically appear in front of the figure and the thousand comma and percentage sign will also be entered automatically. With the percentage figure you still have to enter the decimal point e.g. 0.5 otherwise if you just type the 0 and the 5 it displays as 5%.

If you want the entire string you wrote e.g. +43,852+0.5%, together in one cell, then enter in the ‘type’ box: +0+0.#% The only problem with this I found is that the thousand separator comma doesn’t display. To give the two numbers more space between each other in the column for ease of reading and understanding, you can enter in the 'type' box: +0*(hit the space bar once) +0.#% You can then widen the column as much as you want to drag the figures to opposite sides.

Hope this is of some help.

Hi Purfleet,


I'm a pretty basic user of Excel so it was a straight out copy paste set up. I've never used any of this stuff before so Power Query is totally new to me. I did the basics to start with but couldn't see how to split the columns up. Going to look deeper into this for it seems to be the way to go, get back to you to let you know how I get on.


Best Rgds,
Dave R.

Are you using office 2010 still?



If you are pretty basic i would actually go with the text to columns version


Just highlight the column you want to split, click text to columns and use space as the delimiter

Hi Purfleet,


My Excel is actually 2009 but I found the way to split the column into two. Now that makes things a lot easier, much better than going through the whole list one cell at a time.


Thanks for the tip, put that one into the memory bank for sure.


Best Rgds,
Dave R.

Hi Purfleet,

No, I'm much more updated than Vista, should have updated my stuff ages ago. I'm using Windows 10 and Office365. As I said, I'm pretty basic in my use of Excel and since retiring have no real need for too much and barely scratch the surface as to what it can do, so I'm grateful for any help when needed.

Thanks again,

Best Rgds,
Dave R.

Error in Excel appears if you're running a formula that is referring to a cell that doesn't exist.

Attached Files

ERROR IN EXCEL.xlsx (9.7 KB, 8 views)

Hi amritabansal


Is that a question relating to this thread?