Cell that will not format

[Swoop]

I have an excel spreed sheet that comes in from a outside vendor that apears
to have some set fromatting within a specific cell that I can not correct for
my needs.
It is a numeric formatting (example; 0 2 10700 88807) When there is not a
number with a value higher then a zero in it's place the cell publishes its
value as (example;
0 2 3 42410).

What I would like to do is to format the column to be like this (34000
98777). So I need to replace the foregin value from space to numeric
value.....

any thoughts? Thanks in advance for your help

 

[Peo Sjoblom]

0 2 10700 88807 is not a number per Excel's definition because it has a
space, in fact it has 2 but that doesn't matter, once you have at least one
space it is not a number and when it is not a number it cannot be formatted.
I also fail to see the logic in converting the text strings you have posted
to 34000 98777?

Maybe if you could explain how you can get that value from either

0 2 10700 88807

or

0 2 3 42410

there would be a way of using a text formula to parse the values





--


Regards,


Peo Sjoblom

 

[Swoop]

Thank you for the insight.

The data is sent via an outside source. I have no control over how itis
sent into me. So the format of either: xx xxxxx xxxxx or xx x xxxxx
is not controlled by me.

The format of xxxxx xxxxx does not need to have a space in it to fit my end
users needs (they would just perfer it). The data is used to filter and sort
as well as other search needs.

What I truly need is XXXXXXXXXX dropping the first two numbers and one space.

I have tried to work with character codes to format, but no luck.

Thoughts?

 

[Danny C]

Although you may have done this, I still have to ask.
Did you pick the cell below it and using the right corner, drag it up on top
of the "bad cell". This should completely erase that cell and put in the
"new" cells formatting.

 

[Swoop]

Thank you so much for the insight....

but the whole column is miss formatted. I have tried add a coloumn to the
right of it, format that cell to "special then 0000000000), then custom paste
over the first column....no luck.....

anything else you can thin of?

 

[Peo Sjoblom]

As I said you cannot number format text except for fonts etc, if your data
looks like

02 12345 67891

and you want

1234567891


in a help column put

=RIGHT(SUBSTITUTE(A1," ",""),10)

copy down as long as needed,
then paste special as values.
If you want number values

=--RIGHT(SUBSTITUTE(A1," ",""),10)

custom format

0000000000


or if you want xxxxx xxxxx

use custom format

00000 00000


or the formula


=RIGHT(A1,13)



--


Regards,


Peo Sjoblom

 

[Jon Peltier]

If you have text values in the cell, not numeric values, you can apply
numerical number formats until the cows come home, and the cell text will
not change. This is what Peo is trying to tell you.

- Jon