Populate cell with text based on contents of another cell.

Hi,

I'm sure this is easy to do, but I can't figure it out after several hours of searching the internet.

I've got a column that's got a bunch of different bits of text in it, like "1-CP", "2-BR", etc. I'd like to fill in another cell with a string based on what's in the first cell. So if the first one has "CP" in it, put "Chrome Plated" in the second one, etc.

Manually editing the formula for each one isn't a realistic option (there are over 6,500 rows), so I need something that can look for a list of possible values in the first cell, and put the correct string in the second based on what it finds.

null-OTHER, macOS Sierra (10.12.6), Numbers 4.3

Posted on Oct 21, 2017 11:03 AM

Top-ranking reply

Barry

Level 9

60,023 points

Posted on Nov 27, 2017 1:23 PM

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

View in context

16 replies

Taeraresh Author

Level 1

8 points

Nov 27, 2017 6:26 PM in response to Barry

Okay, testing with your formulas, but I can't get any of them to work. I'm probably doing something dumb.

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). 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.

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

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

And in C2:

IFERROR(VLOOKUP(B2,Finish Codes::A:B,2,FALSE),"not found")

This thread has been closed by the system or the community team. You may vote for any posts you find helpful, or search the Community for additional answers.

Populate cell with text based on contents of another cell.