=Value(text) returns an error

[kent97]

Dear all,

I have copied some data (A1:E13) from internet for information.
I am going to process E2:E13 which look like text, so I put a formula at F2=VALUE(E2) but error.
I expect the results come out as in G2:G13, which carry a decimal when <10, and are integer when >10.

ScreenHunter_041.jpg


Thank you

[JohnTopley]

Missed file ...

[kent97]

Missed file ...

Repost already

[JohnTopley]

..values in E have trailing character: remove these and you will get the numeric value.

[JohnTopley]

Use ..

=VALUE(SUBSTITUTE(E2,CHAR(160),""))

[kent97]

Use ..

=VALUE(SUBSTITUTE(E2,CHAR(160),""))

How about the decimal formatting?

[kent97]

Use ..

=VALUE(SUBSTITUTE(E2,CHAR(160),""))

This formula still return error.

[JohnTopley]

Not on your posted file!

If you don't want to "see" the decimal than format as number with zero decimal places. Or use

=ROUND(VALUE(SUBSTITUTE(E2,CHAR(160),"")),0)

[Lemice]

Try this one then

=LEFT(E2,LEN(E2)-1)*1

Assuming that that special character only appears at the very end. Meanwhile, I can't find that character anywhere from char(160) to char(255), I might run a macro just to find what char it is lol

[kent97]

Try this one then

=LEFT(E2,LEN(E2)-1)*1

Assuming that that special character only appears at the very end. Meanwhile, I can't find that character anywhere from char(160) to char(255), I might run a macro just to find what char it is lol

it works, thank you,
how to set the decimal then?

[Lemice]

The formula returns a number, you can format the entire column however you want (Ctrl 1 - Number)

I still think JohnTopley's formula is much better universally, since it can eliminate that special character no matter where it is, and no matter how many it is in the string.
But I still can't find its number in the ascii table ... Macro said it cannot be found ... Manually index match from Char(1) to Char(255) can't find it either ;w;

[kent97]

The formula returns a number, you can format the entire column however you want (Ctrl 1 - Number)

I still think JohnTopley's formula is much better universally, since it can eliminate that special character no matter where it is, and no matter how many it is in the string.
But I still can't find its number in the ascii table ... Macro said it cannot be found ... Manually index match from Char(1) to Char(255) can't find it either ;w;

But JohnTopley's formula just didn't work, you may test that formula by putting it on cell H2

[Phuocam]

how to set the decimal then?

Format Cells -> Custom -> [<10]0.0;[>10]0

[Lemice]

I meant, when I can tell exactly what ascii number that character has in the table.
inb4 it doesn't exist on the ascii table ...

Anyway, let's hope that "trail" won't appear anywhere else beside at the end lol

[kent97]

Format Cells -> Custom -> [<10]0.0;[>10]0

Yeah! It's exactly what I want.
Thank you Phuocam

[kent97]

I meant, when I can tell exactly what ascii number that character has in the table.
inb4 it doesn't exist on the ascii table ...

Anyway, let's hope that "trail" won't appear anywhere else beside at the end lol

This =LEFT(E2,LEN(E2)-1)*1 already settled my problem. Thank you Lemice

[kent97]

I meant, when I can tell exactly what ascii number that character has in the table.
inb4 it doesn't exist on the ascii table ...

Anyway, let's hope that "trail" won't appear anywhere else beside at the end lol

This =LEFT(E2,LEN(E2)-1)*1 already settled my problem, thank you Lemice

[Phuocam]

Yeah! It's exactly what I want.
Thank you Phuocam

You are welcome!

[Lemice]

Glad that I could help. Don't hesitate if you have any other questions / problems regarding this matter.

I still cannot find that character, I'm mildly annoyed xd

[Lemice]

ops double posted

[JohnTopley]

So the file I posted in #8 was wrong ???????

[Lemice]

So the file I posted in #8 was wrong ???????

For some reason, when I download your file and open it in my computer, as soon as I open it, Excel recalculates everything and returns #VALUE! on all of your formula

[JohnTopley]

I have just downloaded it from my post and it is OK!!!

[Lemice]

Are you sure you F9 / Ctrl = after you downloaded it It doesn't work on my computer, for the 5th times xd

"This file is from da internet, do you wanna edit it?"
"Yes"
"Well damn, here are #VALUE!, have fun!"

I cri