Unable to sort data

[Adnan Halai]

Hi Team,

Greetings for the day.

I have download attached file from MS access which now I want to sort into small to largest.However It is now allowing me to do that.

I have tried to convert text to number by Paste special 1 but it didnt work.

Can someone please help me.

 

[David Evans]

Why don't you convert the text values in Access by using the VAL() function in a query and bring the resulting numbers into Excel?
There's something quirky about what you have in your dataset Annualized Current YTD - I've tried removing the "$" sign and then using value and it's not working properly.

 

[Chihiro]

There's Unicode character between $ sign and 0. It's UNICHAR 8206.

You can use following formula to clean text and turn it into value.
=VALUE(SUBSTITUTE(SUBSTITUTE(A2,UNICHAR(8206),""),"$",""))

However, as David stated, it's better to clean and format text/value in export job from Access DB.

Edit: Since it's consecutive string that you need to substitute, formula can be shortened to...
=SUBSTITUTE(A2,"$"&UNICHAR(8206),"")+0

 

[bosco_yip]

1] Chihiro said that the OP's example having a Unicode character and proposed a UNICHAR formula to work.

However, UNICHAR is a new function in Excel 2013 and Excel 2016.

2] My Excel 2007 detected that it is a CHAR(63),

CHAR(63) is the code for a question mark, and also for characters above the ASCII code 255.

Herein another option formula to remove the special character suitable for all Excel version.

In B2, formula copy down :

=--REPLACE(A2,FIND("$",A2),2,"")

Regards
Bosco

 

[deciog]

Adnan Halai

My contribution

=VALUE(REPLACE(REPLACE(MID(A1,3,20),"",""),"",""))

Copy down


Decio