How to I make 1 000 look like 1000, problems creating scatter graph

Good day to you all.

I have a problem I will try to explain. (appologies if it is poorly written)

At work, we copy some data off a software using Ctrl+C. When then pasting into excel, any number above 1000 will look like this "1 000", "10 000" and so on. Our range is 0-30000 (the range is depth).

When then trying to create a "Scatter with smooth lines" graph, instead of showing depth on the X-axis, it counts the cells. So when cell 6765 has the depth 11000 in it, the corresponding value on the y-axis is shown as 6765.

We have then tried to use a formula to automatically change 1 000 to 1000, and it works fine between 1000-9999.99, >10000 individually; BUT not all together...
We tried this; =IF(A1423<1000;A1423;IF(A1423<10000;REPLACE(A1423;2;1;"");IF(A1423>999;REPLACE(A1423;3;1;""))))

Attached a small example, and it will show you that the X-axis does not show the depth, but it counts cells instead...
We are hoping to have a formula in a template, so when we copy in new numbers, it will automatically make all numbers shown as depth in the graph. There are in total 4 data to be compared on this graph.

Any help is appreciated!

Tigergutt

Does the following in B2 copied down help?

Formula:

Please Login or Register  to view this content.

Thanks for the quick reply Richard!

It seems to have solved the lot! I only for some reason had to chagne , with ; for it to work...

Much appreciated!

Tigergutt

Hi Tiger,

Try using below formula:-
=SUBSTITUTE(A2,"*","")*1

Where " " after A2 is the copy - paste of that extra space

see attached:-
scatter with smooth lines.xlsx

Regards,
DILIPandey
<click on below * if this helps>