Populate cell with text based on contents of another cell.

Hi Taeresh,

Late to the party, and it looks like you and Wayne have found a satisfactory solution. Here's an alternate that will also do the job. I've based it on this statement:

"…[T]here are some that are three digits, but they're still always preceded by a -."

In Table 1, below, the formula shown below the table replaces Wayne's formula in B2 and is filled to the rest of column B. I've used the same examples as Wayne, plus one added to check results for codes not found in the code list.

Table 1::B2: SUBSTITUTE(RIGHT(A2,3),"-","")

Table 1::C2: IFERROR(VLOOKUP(B2,Table 2::A:B,2,FALSE),"not found")

Regards,

Barry

In the second set of tables, I've moved the B2 formula into the C2 formula, replacing the reference to B2, filled the result down column C, then deleted column B:

Table 1::C2: IFERROR(VLOOKUP(B2,Code list::A:B,2,FALSE),"not found")

Table 1A::B2: IFERROR(VLOOKUP(SUBSTITUTE(RIGHT(A2,3),"-",""),Code list::A:B,2,FALSE),"not found")

And in the third set, I've added a 'switch' to suppress calculation until there is an entry in the Full Code column (A) to remove the extra 'not found' results:

Table 1::C2: IFERROR(VLOOKUP(B2,Code list::A:B,2,FALSE),"not found")

Table 1A::B2: IFERROR(VLOOKUP(SUBSTITUTE(RIGHT(A2,3),"-",""),Code list::A:B,2,FALSE),"not found")

Table 1B::B2: IF(LEN(A2)<1,"",IFERROR(VLOOKUP(SUBSTITUTE(RIGHT(A2,3),"-",""),Code list::A:B,2,FALSE),"not found"))

Regards,

Barry

It is unclear the format of the codes, so perhaps you could post back with additional details that show more codes and the string they represent.

Conceptually, you would create a new table that has two columns. Column A contains the code and column B contains the actual string. Then in your first table you would add a formula that looks up the code and retrieves the corresponding string:

Here is how to get just the code :

The full code is in column A

The code to find is in column and the formula that locates the code is:

=RIGHT(A2,LEN(A2)−SEARCH("-", A2, SEARCH("-", A2, 1)+1))

select cell B2, then type (or copy and paste from here) the formula:

=RIGHT(A2,LEN(A2)−SEARCH("-", A2, SEARCH("-", A2, 1)+1))

shorthand is:

B2=RIGHT(A2,LEN(A2)−SEARCH("-", A2, SEARCH("-", A2, 1)+1))

to fill down, select cell B2, copy

then select cell B2 thru the end of column B, paste

To look up the code, create a second table (in my example I named this table "Code List"):

In the first, table, enter the formula:

C2=IFERROR(IF(B2<>"", VLOOKUP(B2,Code List::A:B,2, 0), ""), "<Not Found>")

select C2 and fill down like the previous formula.

Testing with (Barry's) formulas

For the first one, it seems to be grabbing the last several digits of the data, which doesn't work, as the finish codes vary in length (0, PB, 1WH, for example).

The "first one" is a pair of formulas, one in column B, the second in column C.

The first gets the last three 'digits' characters from the full code in column A, and uses SUBSTITUTE to remove any dashes that are included in those three characters. It should return the correct finish code for any pattern (two characters, sometimes three, always immediately preceded by a dash, always at the end of the full code) described to this point in the discussion. It will not work with a one character finish code (unless it is immediately preceded by two consecutive dashes), a two character finish code not immediately preceded by a dash, or a finish code with more than three characters. You cannot get a precise answer without asking a precise question.

I also can't get the second formula to show anything but 'not found'.

The second and third ones both seem to always give 'not found', no matter what's in cell A2.

"not found" is the "IFERROR" result. If there is an error in the formula, or resulting from the formula shown here as "formula":

IFERROR(formula,"not found")

Then the result will be "not found".

IFERROR is used to trap an expected error—the formula not being able to find the searched value in the list of finish codes in column A of the table Wayne and I named 'Code list' and you have named 'Finish Codes'.

For the third one, this is the formula I have in cell B2:

And in C2:

The third example does not use column C.

The second example des not use column C.

The second, as noted above, replaces the reference to B2 in the formula that was (in the first example) in C2, with the formula that had been in B2, where it had created the finish code that reference had been used to collect.

The third is a modification of the second. It checks the content of A2, and suppresses calculation by the rest of the formula if A2 is empty.

Your version: IF(LEN(A2)<1,"",IFERROR(VLOOKUP(SUBSTITUTE(RIGHT(A2,3),"-","",occurrence),Finish Codes::A:B,2,FALSE),"not found"))

Supplied version: IF(LEN(A2)<1,"",IFERROR(VLOOKUP(SUBSTITUTE(RIGHT(A2,3),"-",""),Code list::A:B,2,FALSE),"not found"))

I see only the two differences shown in bold, neither of which seems significant. occurence is an optional argument used to specify a specific instance of the search value ( "-" in this case) is to be replaced. It shows in the copy of the formula only because we have both omitted specifying a value for it (and I deleted it from the pasted copy in my post).

Finish Codes and Code list differ only because you and I have given different names to the lookup table.

As there do not appear to be any syntax errors in your formula, the only reasons for a 'not found' result appear to be:

your lookup table is not named Finish Codes, spelled exactly as in the formula OR

the formula is looking for a finish code that is not in column A of the lookup table.

Regards,

Barry

"It seems to work if there are two dashes in the data, but not one, and both exist in the dataset."

Correct. That's why Wayne asked "Do all the numbers contain 2 dashes?"

I'm going to try out your version and see how that does."

Mine ignores the number of dashes in the full code, but does require that the finish code is either two characters or three characters, and if two, that it is immediately preceded by a hyphen.

Regards,

Barry

The formulas will produce correct results only if the problem description is accurate and complete.

You wrote: " the finish codes vary in length (0, PB, 1WH, for example)"

Wayne's formulas were written following your specification that each finish code was preceded by a dash (and without your later statement that some of the full codes contained only one dash and some contained two).

Mine were written knowing that both full codes with one dash and full codes with two dashes were possible, and with the information that "some codes had three 'digits'" (and some had two).

Both sets of formulas took into account the information that had been provided up to that time. Neither took into account information that had not been provided.

Could we get a complete specification of the full codes that includes all possible variations in the patterns present?

It night be necessary to provide a full list of ALL possible codes, so that Wayne and/or I can examine them and determine what patterns would be useful in extracting the possible finish codes from the full codes.

More later tonight.

Regards,

Barry

is the code you are referring to always the last two digits?

Do all the numbers contain 2 dashes?

"With a bit of editing (removing the second SEARCH bit), that worked perfectly."

This surprised me. The logic of Wayne's formula should lead to the correct result with any of the 'full codes' in the sample, and any others containing two hyphens ( - ), the last immediately before the desired short code.

Here's how the formula works, using the Full Code in A2 of the examples: GH7-G65-BS

Numbers parses formulas from the innermost set of parentheses out, and within each lever, from left to right.

This formula has three levels of parentheses:

RIGHT(A2,LEN(A2)−SEARCH("-", A2, SEARCH("-", A2, 1)+1))

RIGHT(A2,LEN(A2)−SEARCH("-", A2, SEARCH("-", A2, 1)+1))

RIGHT(A2,LEN(A2)−SEARCH("-", A2, SEARCH("-", A2, 1)+1))

Starting at the innermost. Numbers evaluates SEARCH("-",A2,1)

it starts a search at the first (1) character of the string in A2 (GH7-G65-BS), finds the first hyphen at position 4 in the string, returns 4, and the formula moves up to the next level:

RIGHT(A2,LEN(A2)−SEARCH("-", A2, 4+1))

LEN(A2) returns the length in characters ( 10 ) of the string in A2

SEARCH starts a second search of A2 for a hyphen, this time starting at position 5 ( 4+1 ), the first character after the hyphen found earlier. It finds the one at position 8, and returns the value 8, and the formula moves up to the next level:

RIGHT(A2,10−8)

RIGHT gets the contents of A2, does the subtraction, and returns the rightmost two characters of GH7-G65-BS

All of which makes wonder what you meant by "…removing the second SEARCH bit", and what formula was the result "that worked perfectly."

Regards,

Barry