find out if cell contains one of several phrases

Try this formula:

IF(IFERROR(SEARCH("Partially", A1), 0)>0, 1,IF(IFERROR(SEARCH("Not Arrived", A1), 0)>0, 2,IF(IFERROR(SEARCH("Arrived", A1), 0)>0, 3,IF(IFERROR(SEARCH("Completely", A1), 0)>0, 4,""))))

Nicely done, Wayne!

My thoughts turned to FIND, SEARCH and SUBSTITUTE as possible keys to this. SUBSTITUTE would have required a longer formula; FIND about the same.

Regards,

Barry

Hi furbreath,

this part of Wayne's formula is already doing what you want.

(SEARCH("Partially", A1)

quinn

If you don't need instantaneous, totally automatic updating I find that a script can be a little easier for this sort of operation.

Say you have something like this with the text in column A. You can have a script fill out columns B and C:

To run, just copy-paste into Script Editor (in the Applications folder) click once in the table, and click the triangle 'run' button.

SG

set searchPhrases to {"partially", "not arrived", "arrived", "completely"}

tell application "Numbers"

tell front document to tell active sheet to tell table 1

repeat with r from 1 to count rows

repeat with i from 1 to count searchPhrases

if cell ("A" & r)'s value contains searchPhrases's item i then

set cell ("B" & r)'s value to i

set cell ("C" & r)'s value to ¬

offsetof (searchPhrases'sitemi) in ¬

(cell ("A" & r)'s value as text)

exit repeat

end if

end repeat

end repeat

end tell

end tell

Or if you have only one cell to analyze:

set searchPhrases to {"partially", "not arrived", "arrived", "completely"}

set targetCell to "A1"

tell application "Numbers"

tell front document to tell active sheet to tell table 1

repeat with i from 1 to count searchPhrases

if celltargetCell'svalue contains searchPhrases'sitemi then

set cell "B1"'s value to i

set cell "C1"'s value to ¬

offsetof (searchPhrases'sitemi) in ¬

(cell targetCell's value as text)

exit repeat

end if

end repeat

end tell

end tell

Hi furbreath,

It might be time to provide the big picture and describe what you are actually trying to do. Screenshots would help.

quinn

Hi furbreath,

That looks crazy to me and It doesn't work on my machine but if it does the job for you then there you have it.

quinn

Hi Furbreath,

This can be done but it is tedious and awkward to do. Having the data separated by multiple spaces does not make it easier to access later on. Patterns in your data that are consistent can be used. Is the initial number code always the same length, Are your sentences always the same 4, does the first number always only have one digit after the dot does the next always have 2, are ther only 3 periods in each line?

You are still not providing the big picture here. How are you using each ofthese pieces of data? If there are some you don't need don't spend the time teasing them out. Check out the functions available under Text in the function browser. in additon to the ones Barry mentionsed above examine LEFT(), MID() and RIGHT().

quinn

furbreath wrote:

Here is a sample of what might be in cells A1 through to A4 :

022-39567780 YYZ CDG 7 1091.0 2.94 Completely assigned to the booked flight. SCR LOGISTICS CDA INC 2

022-36952915 YYZ NTE 7 841.0 2.22 Goods have arrived at the station. BEMACER 2

022-39549720 YYZ SXB 7 369.0 .47 Completely assigned to the booked flight. ORC OVERSEAS RETRO CONSOLIDATORS 3

023-39147743 YYZ TLS 5 803.0 2.65 Completely assigned to the booked flight. STL CARGO AIR CARGO LOGISTICS INC 1

I need to parse out all what is considered data so that I can manipulate it further with formulas. As an example this is how I would like the first line to be separated :

022-39567780 YYZ CDG 7 1091.0 2.94 Completely assigned to the booked flight. SCR LOGISTICS CDA INC 2

Assuming your sample is representative of your data, i.e. you have strings that you need to parse into separate columns, you will probably find it easier to use a script.

The one below will do this:

To use, you copy-paste the script into Script Editor (in the Applications > Utilities folder), and in this example select cells A1:A4 (the ones with the strings you need to parse) and click the 'run' button. After the prompt in this example I clicked once in cell B1 and typed command-v to paste. You could paste somewhere else as well. That's it! Post back if you have any problems.

SG

tell application "Numbers"

tell front document to tell active sheet

tell (first table whose selection range's class is range)

set selRng to selection range

set pasteStr to ""

repeat with c in selRng's cells

set pasteStr to pasteStr & my parseText(c'svalue) & return

end repeat

end tell

end tell

end tell

set the clipboard topasteStr

display notification "Click once in Numbers cell and command-v to paste"

return pasteStr

to parseText(textToParse)

try

set col1 to textToParse's text 1 thru 12 --> "022-39567780"

set col2 to textToParse'sword 3 --> "YYZ"

set col3 to textToParse'sword 4 --> "CDG"

set col4 to textToParse'sword 5 --> 7

set col5 to textToParse'sword 6 --> "1091.0"

set col6 to textToParse'sword 7 -->"2.94"

set col8 to textToParse's characters ((offset of ". " in textToParse) + 2) thru -1 as text ¬

-->"SCR LOGISTICS CDA INC 2"

set col7Start to (offset of col6 in textToParse) + (length of col6) + 1 --> 37

set col7End to (offsetofcol8intextToParse) - 2 --> 79

set col7 to textToParse'scharacterscol7Start thru col7End as text ¬

--> "Completely assigned to the booked flight."

return col1 & tab & col2 & tab & col3 & tab & col4 & tab & col5 & tab & col6 & tab & col7 & tab & col8

on error

display dialog "Select only cells with data you want to parse." buttons "Cancel"

end try

end parseText

furbreath wrote:

The scripts are good to know however I need the results to show immediately so formulas are the way to go for me.

Actually, I wouldn't think of this as an either-or situation. A script can be easier for splitting a string into separate cells. It does its thing in a second or so. Formulas are then applied to sum, count, etc.

I guess your workflow and solution will remain a mystery.🙂 Glad you were able to find something that works for you.

SG