You will need to determine when and what is to be copied (via formula) to the new column. Whatever it is, it needs to be the same for all rows. Then we can come up with a formula.
- Get the last word in the string of text?
- Get the last word but only if there is more than one word? Otherwise nothing.
- Get the third word from the string of text (or nothing if there are not three words)
- Separate the string into words; one column per word, up to three words (three columns). Any leftover words go into a fourth column
Okay, further clarification with the requested screen shot.
Regarding the first fully visible row: the second column contains the words "Goldengate Guitar Case 000." This is the information I received from my vendor and I added all the other columns so that this product will be searchable in my point of sale system. Basically I have been manually adding all the other fields using data that is already contained in the first row. "Case" has gone into the preceding column, "hardshell" into a next column, "000" after that, ect. Is there a way to write a formula to do this for me. For instance, could I tell the system to create a new cell that contained the word "case" everytime it saw the word "case" in the first column? If it can't do that I thought the next easiest thing would be just to group all the rows that contain "case" and that way I could quickly add a new column to coordinate with the first one and add "case" by dragging down. As it is, there are cells containing "case" all through my document so it's very time intensive.
You can extract the word case where the string contains case using
But that doesn't solve the issue of what goes in that column for lines where the product ins not a case.
Looking at the other columns, I don't see a means of getting "Hardshell" from the given data using a formula.
A semi-manual method using the "Show rows... feature of the Reorganize panel might prove efficient.
Here, the Show rows... displayd was applied, then CASE was entered in the first cell in column A and filled down to the last visible cell. As can be seen below, the fill operation placed "CASE" in only the rows where column B containsed "CASE".
We don't see enough of your table to know what columns are in your screen shot. I'll assume that the leftmost column is Column A and the column with the long description of the case is Column B. You can make adjustments for the actual column IDs.
To copy "Case" from column B to a dedicated column for the Case notation, you can use:
=IF(ISERROR(FIND("CASE", B)), "", "Case")
I'll describe the expression because, using a sort of inverse logic, it can be difficult to suss out.
The FIND function returns the starting characteer number of the string "Case" if "Case" is found. If not found, an Error is generated. The function ISERROR returns the logic state TRUE when "Case" isn't found. The IF function is written to return a null string when "Case" isn't found and to return "Case" when it is found.
You can use this expression in other columns by substituting what you are looking for in the FIND function and substituting what you want to write in the new column in the ELSE agrument of the IF statement - the second instance of "Case" in this example.
I'm sorry to say that I think this is a nearly impossible task. It takes one function to find a single word. It would take multiple copies of that function, each looking for a different word, to find all the words you might be looking for. Then there is the problem where you might find a match that isn't really a match. An example would be when searching for the word "case" in the following made-up item:
Caseman Corporation Guitar Picks
It would find the word "case" even though the item is not a case.
I started expanding on Barry's and Jerry's work, using a "lookup" table and multiple copies of the FIND formula, but then I came across the problem with false matches. To fix that it gets much more complicated and much longer and, therefore, much slower. There is also a practical limit to how many words you can choose from (Case, Bag, Pick, String, Strap, etc.). Once you get past ten or so for a particular column, the formula is horrendously long. The practical limit would be smaller for a formula that avoids false matches.
I think it might be possible in Excel with its array formulas. An array formula would look basically like a single copy of the FIND formula given by Barry and Jerry but would operate on an entire column of words in a "lookup" table.
Thanks, Jerry! Here is a new scenario along the same lines:
In this instance I'm working from an exported list from Quickbooks. Quickbooks has grouped the category (which I'm relabeling "class") in with the product code. I want to separate these two elements. So I want to subtract "Cases:" and "Amplifiers:" from column A and add "Case" and "Amplifier" to column B. Is there a way to write a formula to do this in my entire document no matter what the category is (i.e. "Cases:" "Amplfiers:" or "Guitars:") ?
This appears to require the relatively simple task of pulling out the first word of the string. Much easier than looking for a specific words that can be anywhere in a string.
This might do it:
It returns everything left of the colon or, if no colon, it returns the entire string.
That won't do it, Badunit, unfortunately. Using that formula I cannot change the word being inserted into the new column (i.e. from "Cases" to "Case") and that also won't allow me to take the word "Cases" out of the first column (which I want to do, leaving only the product number).
Going by the few items you showed in your screenshot, all of them have an "s" after them. You can easily remove that "s" by changing the "1" to a "2" in the formula. I say that but I'm not convinced all your items really end with an "s".
You cannot take the word "cases" out of the first column because that column is data you (Quickbooks) have entered. What you might be able to do is use another column with a formula that pulls the product number out of column A just like we are pulling out the "class". I say "might" because it looks like sometimes the product number is before the product description and sometimes it is after. That would make it nearly impossible to do. If those two things together are the "product number" then it should be pretty simple.
I'm with Jerry on the "one inch at a time". I think you need to more fully define the problem and all the test cases. Right now we are just guessing and we seem to be hitting foul balls because we don't know in which direction the field is.
Not sure which inch of the tail we're currently working on, but here's a revision of Badunit's formula for the left side, and a related formula which will return everything to the right of the (first) colon:
Left (eg. "Case") =IFERROR(LEFT(A,FIND(":",A)-2),LEFT(A,LEN(A)-1))
Right (eg. "USB-8E") =IFERROR(RIGHT(A,LEN(A)-FIND(":",A),"")