Trim and Substitute not working

[Fuzzy]

I have this excel with the following data in column A

'1234567891234545
236556545454554554
'45454545455458475258
4845487845155484844
4848745125154545454

I want to do the following:

1) remove the extra space dat comes in the front of a few numbers.
I've tried trim - it dint work.
I've tried substitute - it dint work.
I've tried text to column - dint work either.
I've tried find and replace - it still dint work.

Ok wat worked is this - I copied the space from the function bar, and then i
did a find and replace of the copied space - it worked!
However, the number got converted into its exponential form.... smthg like
236556545E0...
I want to remove the space and keep the number as is. Please help!

2) i want to remove the single quotes before the number.
I did a find and replace.... the excel did not find any quotes.
I tried text to column and converted it to general. This removed the quotes.
but once again, changed the number to the exponential form.... or sometimes,
just rounds off the last digit to 0...

i want to remove the quotes without it changing the numbers.

 

[Ms-Exl-Learner]

Just place the cursor on that cell and Press F2 now go to that unknown
character (i.e.) space and copy it.

Now in a fresh cell Type =CODE(""), paste that unknown character inside the
"" (double quotes). Now it will show like this =CODE(" "). Press enter and
you will get the code of that Unknown Character.

Now in your substitute formula mention that code CHAR(32) like the below:-
=SUBSTITUTE(A1,CHAR(32),"")

Here the code 32 is refers to Blank space.

Remember to Click Yes, if this post helps!

 

[David Biddulph]

If you want to keep the data as numbers, but display not in exponential form
but as a number with no decimal points, format as number with no decimal
points. If you want it as text showing the number with no decimal points,
use =TEXT(A1,"0")

I guess that the reason that you were struggling with your substitute or
find and replace when you were looking for a space was that it wasn't a
space [CHAR(32)], but more likely a non-breaking space [CHAR(160)].

In the cases where you formatted to general and it rounded off the last
digit to zero, you presumably started with more than 15 digits. Excel
stores numbers to 15 significant figures. If you want more than 15 figures,
then it needs to be text, so don't try to convert it to a number.
Presumably the reason for the apostrophe being there in the first place was
to make it text, not a number.

 

[Fuzzy]

Thank you sooo much!
The code worked!

However, after i get the data, and i do a copy paste special - values on the
new column, all numbers automatically get pre-fixed with '.

 

[David Biddulph]

You've replied to yourself, rather than to anyone else's contribution to the
thread, so it isn't clear whose suggestion you've used. As I said in my
message earlier, the preceding apostrophe will ensure that the data will be
treated as text rather than a number, so will avoid some of the problems you
originally reported.