Problem with numbers being read as text

[Oscar_Sabio]

Hello everyone

I am having a problem converting numbers stored as text to numbers.

There is a data dump from a system that bring the numbers, yet Excel sees them as text and I am having a tough time converting them to numbers.

I have tried:
- Value formulae
- Paste Special by multiplying by 1, or adding a blank cell

nothing works

Can anyone help?

I have attached a workbook, it is the column A I am having a problem with.

Many thanks

oscar

 

[bobhc]

Select the column and then choose number in the number section of the ribbon any input will be converted to numbers. Just reduce the decimal point to zero

 

[Khalid NGO]

Hi Oscar, Welcome to the forum

The problem I see is that there is a non-visible character at the start and also at the end of each cell.

Try using a helper column, say column D, enter this formula in D2:

=--RIGHT(LEFT(A2,8),7)

Copy down as required, than use copy/paste special values (if required)

Regards,

 

[Naresh Yadav]

Dear Oscar,

you can also try =MID(A2,2,7)+0

 

[Khalid NGO]

Or try this using replace formula:
=--REPLACE(REPLACE(A2,1,1,),LEN(A2)-1,1,"")

Regards,

 

[trprasad78]

Hi Oscar, Welcome to the forum

The problem I see is that there is a non-printing character at the start and also at the end of each cell.

Try using a helper column, say column D, enter this formula in D2:

=--RIGHT(LEFT(A2,8),7)

Copy down as required, than use copy/paste special values (if required)

Regards,

This one is very useful, May i know what is the use of "=--"
where are other place we can use this.

 

[Khalid NGO]

May i know what is the use of "=--"
where are other place we can use this.

Hi Parsad,

Left/Mid/Right etc are basically Text function, when we use these functions result produce in Text values instead of number values.

We use -- to convert numbers which looks like numbers but aren't.

We can replace -- with:

Value()
+0
*1
^1

One more thing, I had hard-coded the length in above formula, use this instead:
=RIGHT(LEFT(A2,LEN(A2)-1),LEN(A2)-2)+0

Or Naresh's formula:
=MID(A2,2,LEN(A2)-2)+0

Regards,

 

[trprasad78]

Hi Parsad,

Left/Mid/Right etc are basically Text function, when we use these functions result produce in Text values instead of number values.

We use -- to convert number which looks like number but aren't.

You can replace -- with:

Value()
+0
*1
^1

One more thing, I had hard-coded the length in above formula, use this instead:
=RIGHT(LEFT(A2,LEN(A2)-1),LEN(A2)-2)+0

Or Naresh's formula:
=MID(A2,2,LEN(A2)-2)+0

Regards,

Thank you so much for detail explanation, I try Naresh's formula too.

 

[Oscar_Sabio]

Thank you very much to everyone. That was perfect!