Converting Data into number Data

  • Re: Converting Data into number Data


    Normally you would use something like
    [bfn]=SUBSTITUTE(B5, " ", "")[/bfn]


    However, the first 2 values in your sample do not have a normal space as the 3rd character but they have a 'Non-Breaking Space'. You will have to replace all of these using a nested SUBSTITUTE() formula and multiplying by 1 to return a number rather than text


    [bfn]=SUBSTITUTE(SUBSTITUTE(B1, CHAR(160), " "), " ", "") * 1[/bfn]
    You can then format the cell to display the value as you wish

  • Re: Converting Data into number Data


    Where or how do you get the value in the first place - is it an import from a file? Unusual character to have in the cell. As cytop said you have ASCII 160 - if you want the specific format you could
    =TEXT(SUBSTITUTE(SUBSTITUTE(B3,CHAR(160),"")," ",""),"#,##00")


    However this is a text - cytop has a better solution in that you should format the cell.

    Regards
    [SIZE=3]Anthony
    [/SIZE]&WCF_AMPERSAND[SIZE=3]
    [/SIZE]&WCF_AMPERSAND&WCF_AMPERSAND&WCF_AMPERSAND[SIZE=2]You have your way. I have my way. As for the right way, the correct way, and the only way, it does not exist.[/SIZE]




  • Re: Converting Data into number Data


    Hi Guys


    Thanks for the help. The values where obtained from a word document where the typist inserted spaces between the numbers

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!