Skip navigation
This discussion is archived

Point to comma

3152 Views 10 Replies Latest reply: Dec 17, 2009 5:21 AM by mlldmtn RSS
mlldmtn Level 1 Level 1 (0 points)
Currently Being Moderated
Dec 16, 2009 3:55 AM
Hi,
I'm trying to form a graph out of two columns but in one of them the numbers are in points, that is, 22.2, and the other column has numbers separated by commas, 22,2. I need to change the column with points to commas so that I can create a working graph. As there are several thousand number, I can obviously not change everything manually. I just wonder whether there is a way numbers can "auto-change" points to commas.
Macbook pro 13" mid-209, Mac OS X (10.6.1)
  • Level 8 Level 8 (41,760 points)
    Currently Being Moderated
    Dec 16, 2009 5:08 AM (in response to mlldmtn)
    Three tracks come to my minds

    (1) Use Find & Replace
    (2) Use a formula in an auxiliary column:
    =LEFT(B,SEARCH(".",B,1)-1)&"."&RIGHT(B,LEN(B)-SEARCH(".",B,1))
    (3) Use an AppleScript

    For sure, if you are satisfied with tracks 1 or 2 I will not spend time to build a script.

    Yvan KOENIG (VALLAURIS, France) mercredi 16 décembre 2009 14:08:13
    To be the AW6 successor, iWork MUST integrate a TRUE DB, not a list organizer !, Mac OS X (10.4.11)
  • Jerrold Green1 Level 7 Level 7 (28,290 points)
    Currently Being Moderated
    Dec 16, 2009 7:21 AM (in response to mlldmtn)
    mm,

    An X-Y Scatter Chart in Numbers doesn't care if your decimal indicator is different in one column vs. the other. Category Charts do seem to have a problem.

    To answer your question more directly, I'm a fan of using Pages to reformat data. Here's what I would do...

    Copy the column in Numbers and Paste into Pages. Select the table in Pages and Format > Table > Convert Table to Text. Use Find/Replace to replace the periods with commas. Select the text and Format > Table > Convert Text to Table. Copy Table in Pages, Paste back into Numbers.

    This give you an option.

    Jerry
    iMac G5 2G RAM, Mac OS X (10.5.8)
  • Badunit Level 6 Level 6 (10,775 points)
    Currently Being Moderated
    Dec 16, 2009 10:59 AM (in response to Jerrold Green1)
    This may be a dumb question but how does one go about having numbers with a decimal point separator in one column and a comma in another unless one of those columns is formatted as text? I thought the decimal separator was based on the international settings. Do Swedish settings allow both?

    If it is possible to have such an arrangement, would use of a third column, C, with the formula =B*1 result in conversion of the commas to points or points to commas? Then copy and paste values back to the original column.
    Mac Pro 2008, Mac OS X (10.5.6)
  • Level 8 Level 8 (41,760 points)
    Currently Being Moderated
    Dec 16, 2009 11:32 AM (in response to Badunit)
    Hello

    There is no setting allowing us to work with two different decimal delimiter.
    I assume that the OP grabs datas from two sources.
    I'm French and here, the decimal delimiter is the comma but I often grabs datas from English sites which use the decimal period.
    When I import such datas thru an AppleScript, I take care to pass numerical values to Numbers after coercing them as text.
    Doing that, every decimal number is automatically normalized to the in_use decimal delimiter.

    It seems that the OP doesn't use such a path so, he is forced to do the normalization thru an other path.

    In my first responce I listed three paths.
    the third one was an Applescript.

    The basic component of such a script would be :

    --

    tell application "Numbers" to tell document 1 to tell sheet 1 to tell table 1
    tell cell 2 of column 2 to set value to ((get value) as real) as text
    end tell
    --


    Yvan KOENIG (VALLAURIS, France) mercredi 16 décembre 2009 20:32:34
    To be the AW6 successor, iWork MUST integrate a TRUE DB, not a list organizer !, Mac OS X (10.4.11)
  • Jerrold Green1 Level 7 Level 7 (28,290 points)
    Currently Being Moderated
    Dec 16, 2009 12:12 PM (in response to Badunit)
    Badunit,

    I'm glad you checked me on that one. I believe that between the bifocals and picking some values that didn't properly demonstrate the issue, I fooled myself into thinking that I had commas working as decimal separators. What I did have, which still baffles me, was commas that were being ignored, so 1,00 was treated as 100, for instance. My apologies to the OP.

    Jerry
    iMac G5 2G RAM, Mac OS X (10.5.8)
  • Badunit Level 6 Level 6 (10,775 points)
    Currently Being Moderated
    Dec 16, 2009 1:28 PM (in response to mlldmtn)
    That is exactly what I found when converting textual numbers with a comma decimal separator to numbers with a decimal point; the comma was ignored. I was going to mention that in my previous post but figured I would wait until I found out if the numbers were text or not.

    If all of the numbers have a single decimal place, no more, no less, you can convert them with the formula =B/10 (assuming they are in column B). If the number of decimal places varies, you can copy the column to another table on another sheet and do Find&Replace/Current Sheet Only to convert commas to points then copy/paste back to the original table and change the format to Automatic or Number.

    If your numbers include a thousands separator and if it is a decimal point, you will want to convert it to nothing before converting the commas. If it is a space, you will want to convert it to nothing but you can do that after converting commas. I don't know all the localizations so I'm trying to cover all bases here.

    Of course, if you will need to do this with additional documents, an Applescript may be the best workflow.
    Mac Pro 2008, Mac OS X (10.5.6)
  • Level 8 Level 8 (41,760 points)
    Currently Being Moderated
    Dec 17, 2009 2:56 AM (in response to Badunit)
    Here in France, changing the decimal separator with a formula behaves flawlessly.



    The conversion formula replace the period by a comma.

    For an English user, it would be necessary to apply the other change : replace the comma by a period in the column containing decimal commas.
    If ou replace the period by a comma, it will be deciphered as a thousands separator which explain what you get.

    This English formula replaces a decimal period by a comma.
    =LEFT(B,SEARCH(".",B,1)-1)&","&RIGHT(B,LEN(B)-SEARCH(".",B,1))
    It may be useful for a Norwegian using Numbers in English on a system using the decimal comma.

    This English formula replaces a decimal comma by a period.
    =LEFT(B,SEARCH(",",B,1)-1)&"."&RIGHT(B,LEN(B)-SEARCH(",",B,1))
    It may be useful for an English using Numbers in English on a system using the decimal comma.

    I will post a script soon.

    Yvan KOENIG (VALLAURIS, France) jeudi 17 décembre 2009 11:48:33
    To be the AW6 successor, iWork MUST integrate a TRUE DB, not a list organizer !, Mac OS X (10.4.11)
  • Level 8 Level 8 (41,760 points)
    Currently Being Moderated
    Dec 17, 2009 3:23 AM (in response to KOENIG Yvan)
    Here is the script.

    --

    --[SCRIPT normalize_Decimal]

    (*
    Enregistrer en tant que Script: normalize_Decimal.scpt
    déplacer le fichier créé dans le dossier
    <VolumeDeDémarrage>:Users:<votreCompte>:Library:Scripts:Applications:Numbers:
    Il vous faudra peut-être créer le dossier Numbers et peut-être même le dossier Applications.

    Selectionner un bloc de cellules à traiter.
    menu Scripts > Numbers > normalize_Decimal

    Le script rétabli les nombres du bloc au format local en vigueur.

    --=====

    L'aide du Finder explique:
    L'Utilitaire AppleScript permet d'activer le Menu des scripts :
    Ouvrez l'Utilitaire AppleScript situé dans le dossier Applications/AppleScript.
    Cochez la case "Afficher le menu des scripts dans la barre de menus".

    +++++++

    Save the script as Script: normalize_Decimal.scpt

    Move the newly created file into the folder:
    <startup Volume>:Users:<yourAccount>:Library:Scripts:Applications:Numbers:
    Maybe you would have to create the folder Numbers and even the folder Applications by yourself.

    Select a range of cells to normalize.
    menu Scripts > Numbers > normalize_Decimal

    The script apply to the numbers of the range the local decimal setting in use.

    --=====

    The Finder's Help explains:
    To make the Script menu appear:
    Open the AppleScript utility located in Applications/AppleScript.
    Select the "Show Script Menu in menu bar" checkbox.

    --=====

    Yvan KOENIG (VALLAURIS, France)
    2009/12/17
    *)
    property scan2bottom : true
    (*
    true = scan from the selected row to the bottom of the table
    false = scan the selected rows
    *)
    --=====

    on run
    set {dName, sName, tName, rname, rowNum1, colNum1, rowNum2, colNum2} to my getSelParams()
    tell application "Numbers" to tell document dName to tell sheet sName to tell table tName
    if scan2bottom then
    set rowMax to count rows
    else
    set rowMax to rowNum2
    end if
    repeat with c from colNum1 to colNum2
    tell column c
    repeat with r from rowNum1 to rowMax
    tell cell r
    set maybe to (get value)
    if maybe is not 0.0 then
    try
    set value to (maybe as real) as text
    end try
    end if -- maybe
    end tell -- cell
    end repeat -- r
    end tell -- column
    end repeat -- c
    end tell -- tell
    end run

    --=====

    on getSelParams()
    local r_Name, t_Name, s_Name, d_Name, col_Num1, row_Num1, col_Num2, row_Num2
    set {d_Name, s_Name, t_Name, r_Name} to my getSelection()

    if r_Name is missing value then
    if my parleAnglais() then
    error "No selected cells"
    else
    error "Il n'y a pas de cellule sélectionnée !"
    end if
    end if

    set two_Names to my decoupe(r_Name, ":")
    set {row_Num1, col_Num1} to my decipher(item 1 of two_Names, d_Name, s_Name, t_Name)
    if item 2 of two_Names = item 1 of two_Names then
    set {row_Num2, col_Num2} to {row_Num1, col_Num1}
    else
    set {row_Num2, col_Num2} to my decipher(item 2 of two_Names, d_Name, s_Name, t_Name)
    end if
    return {d_Name, s_Name, t_Name, r_Name, row_Num1, col_Num1, row_Num2, col_Num2}
    end getSelParams

    --=====
    (*
    set {rowNumber, columnNumber} to my decipher(cellRef,docName,sheetName,tableName)
    apply to named row or named column !
    *)
    on decipher(n, d, s, t)
    tell application "Numbers" to tell document d to tell sheet s to tell table t to return {address of row of cell n, address of column of cell n}
    end decipher

    --=====
    (*
    set { d_Name, s_Name, t_Name, r_Name} to my getSelection()
    *)
    on getSelection()
    local _, theRange, theTable, theSheet, theDoc, errMsg, errNum

    tell application "Numbers" to tell document 1
    repeat with i from 1 to the count of sheets
    tell sheet i
    set x to the count of tables
    if x > 0 then
    repeat with y from 1 to x
    try
    (selection range of table y) as text
    on error errMsg number errNum
    set {_, theRange, _, theTable, _, theSheet, _, theDoc} to my decoupe(errMsg, quote)
    return {theDoc, theSheet, theTable, theRange}
    end try
    end repeat -- y
    end if -- x>0
    end tell -- sheet
    end repeat -- i
    end tell -- document
    return {missing value, missing value, missing value, missing value}
    end getSelection

    --=====

    on decoupe(t, d)
    local l
    set AppleScript's text item delimiters to d
    set l to text items of t
    set AppleScript's text item delimiters to ""
    return l
    end decoupe

    --=====

    on parleAnglais()
    local z
    try
    tell application "Numbers" to set z to localized string "Cancel"
    on error
    set z to "Cancel"
    end try
    return (z is not "Annuler")
    end parleAnglais

    --=====
    --[/SCRIPT]
    --


    Yvan KOENIG (VALLAURIS, France) jeudi 17 décembre 2009 12:22:42
    To be the AW6 successor, iWork MUST integrate a TRUE DB, not a list organizer !, Mac OS X (10.4.11)

Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Legend

  • This solved my question - 10 points
  • This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.