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.

    [/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!