Barntonjac
Level 1 (8 points)
iWork

Q: Can I use Search or Find to locate a comma or inverted commas in a text

I was wanting to use formulas to locate commas etc in text strings. I'm new to mac and numbers and I can't find the correct way of entering the search-string when looking for commas or inverted commas. Always get invalid format message.

iMac with Retina 5K display, iOS 9.3.5

Posted on Sep 2, 2016 9:43 AM

by Barntonjac,Solvedanswer
Barntonjac Barntonjac
Level 1 (8 points)
iWork
A:

Thank you again SGlll. That solved the problem completely! I was having trouble getting CSV files into Numbers but a few formulae have allowed me to separate strings of text into the appropriate columns thanks to your help.

 

Barntonjac

Posted on Sep 2, 2016 12:49 PM

Close
Barntonjac

Q: Can I use Search or Find to locate a comma or inverted commas in a text

  • All replies
  • Helpful answers

  • by SGIII,Helpful

    SGIII SGIII Sep 2, 2016 11:46 AM in response to Barntonjac
    Level 6 (10,622 points)
    Mac OS X
    Sep 2, 2016 11:46 AM in response to Barntonjac

    One way is with FIND, like this:

     

    Screen Shot 2016-09-02 at 2.08.35 PM.png

     

       =FIND(",",A2)

     

    That shows that the comma is the 12th character in the string.

     

    This assume you use . not , in your region as the decimal separator.

     

    If you use , as the decimal separator then I think the formula would be:

     

       =FIND(",";A2)

     

    SG

  • by Barntonjac,

    Barntonjac Barntonjac Sep 2, 2016 11:44 AM in response to SGIII
    Level 1 (8 points)
    iWork
    Sep 2, 2016 11:44 AM in response to SGIII

    Thanks SGlll,

     

    That works fine! For the particular task I'm on now, I also need to identify the position of the " sign in a text. Unfortunately, using Find(""",A2), in an analogous way to locating a comma, just won't work. I have a feeling that it's just not going to be possible to do this in Numbers.

     

    Regards

    Barntonjac

  • by SGIII,

    SGIII SGIII Sep 2, 2016 12:04 PM in response to Barntonjac
    Level 6 (10,622 points)
    Mac OS X
    Sep 2, 2016 12:04 PM in response to Barntonjac

    Barntonjac wrote:

     

    Thanks SGlll,

     

    also need to identify the position of the " sign in a text. Unfortunately, using Find(""",A2), in an analogous way to locating a comma

     

    Yes, that can be a problem. But you can try this:

     

    =FIND(CHAR(34),A2)

     

    CHAR takes the number and converts to the ASCII character. To find out what number you need to put in CHAR() you can type the target character in a cell, let's say A1 just as an example, and get the code using CODE(A1).  Then put that number in CHAR(). Or look up the number in a table like this. Not that there are "straight" quotes as in this example and also "curly" quotes with 8220 and 8221, etc.

     

    SG

  • by Barntonjac,Solvedanswer

    Barntonjac Barntonjac Sep 2, 2016 12:49 PM in response to SGIII
    Level 1 (8 points)
    iWork
    Sep 2, 2016 12:49 PM in response to SGIII

    Thank you again SGlll. That solved the problem completely! I was having trouble getting CSV files into Numbers but a few formulae have allowed me to separate strings of text into the appropriate columns thanks to your help.

     

    Barntonjac