Here are some learnings from a recent project I worked on. We needed to take our order data and convert it into a format that our co-packer could use in their fulfillment center. You can find the example sheet with dummy data here.
I found it expedient to have three different tabs: the first contained my input data that I had pulled from our shipping software, the second one was a work tab where I squeezed that data into intermediate forms, and the last was my output tab that I sent to the co-packer’s fulfillment department.
My primary challenge was that the input data had all of the individual products and counts in one cell for each customer in one cell. I needed to split this into a tally sheet with the product name in the column header and counts for each meal that the customer ordered that.
How to Separate Full Names into First Name and Last Name in Google Sheets – SPLIT
My input data had the full name in one cell. I needed to take it and separate the last name and the first name into separate cells. So I started with a simple SPLIT function:
Input: Adam Smith (one cell)
Formula: =SPLIT(Input!B2,” “)
That says “Take the string in B2 on the Input tab and split it every time there is a space”.
Output: Adam (first cell) Smith (second cell)
How to Separate Full Names into First Name, Middle Name, and Last Name in Google Sheets
I ran into a bump on the name split. Some full names had middle names or initials that would output into 3 cells. But I only needed the first and last names. I was good to on first names but needed a formula to split just the last name into it’s own distinct column. So I did some Googling and found an answer in the help forum.
Input: Tracy L Anderson
Formula: =TRIM( RIGHT( SUBSTITUTE(Input!B2 , ” ” , REPT( ” ” , 100 ) ) , 100 ) )
I don’t really know how this formula works. I understand TRIM and RIGHT but not so much SUBSTITUTE or REPT. But it worked for me so I ran with it.
Output: Anderson
After this I used a simple cell reference in another sheet (=Work!E2) to get it into the Output tab.
How to Split Multiple Lines in a Cell into Separate Cells in Google Sheets – CHAR(10)
All of the product data was in one cell. Each product was separated by a line break. My first step was to split it apart by that line break. Google sheets has an option to split cells into columns, but that would have put everything into it’s own cell. The trick is to use CHAR(10) to indicate the line break.
Input:
PBG-012 Machaca x1
PBM-055 Braised BBQ Beef Brisket x5
Formula: =SPLIT(C2,char(10))
This says “Take C2 and split it by line breaks”
Output: PBG-012 Machaca x1 (first cell) PBM-055 Braised BBQ Beef Brisket x5 (second cell)
There were some trailing spaces so I used the TRIM function to take those off. To get TRIM and SPLIT to work together you have to use ARRAYFORMULA.
Basic formula: =TRIM(C2)
Formula I used: =ARRAYFORMULA(TRIM(SPLIT(C2,char(10))))
For some customer orders, the products aren’t specified by the customer and we pick the meals that go in their shipment. My export data would have a blank product cell in those cases. The above formula will throw an error message if it’s set against a blank cell. So I used IFERROR to display the text ‘WE PICK’ in those cases.
Input: (Blank Cell)
Basic Formula: IFERROR(C2, “WE PICK”)
This says, “If there is an error in C2, display WE PICK”.
Output: WE PICK
Final Formula: =IFERROR(arrayformula(trim(SPLIT(Input!C2,char(10)))),”WE PICK”)
This says, “Split the data in C2 on the Input tab at line breaks. Since there are multiple cells now, I want you to look at all of them (arrayformula). Trim off any trailing or leading spaces. If the cell you are pulling from is blank, return WE PICK instead of an error message.”
How to Find Text or Numbers in a Range of Cells in Google Sheets – HLOOKUP (row) VLOOKUP (column) +
How to Extract Text or a Number from the End of a Cell – RIGHT
This was the hardest thing for me to figure out. I spent several hours googling and reading through forums. I tried REGEXMATCH, FIND, SEARCH, LOOKUP, and wasn’t able to get anywhere. Finally I started diagramming it out on a sheet of paper. I broke it down into different components. Once I got it out of my head I was able to attack the individual components, experiment, and find a relatively simple solution. For whatever reason, reading this article made it all click together for me. Lesson learned: Better thinking = better planning = better solutions.
Part 1: Find the product name in a row of cells
I had already split all the products in one cell apart into separate cells. Now I needed to be able to find the right product. I used HLOOKUP because I was searching a row (you would use VLOOKUP if you were searching a column). I wanted to start at the 2nd cell and search the entire row (F2:2). I didn’t wanted save typing time on the search string match so I used wildcards.
Normally VLOOKUP is used to find a header and then output the text or number below it. But I wanted to output the text inside the cell. So I had the third parameter = 1 so that it would look in the cell. Finally, VLOOKUP has a parameter that specifies whether the search area is sorted, mine was not so I set it to ‘false’.
Input: PBG-012 Machaca x1 PBM-055 Braised BBQ Beef Brisket x5 PCM-142 Lemon Pepper Chicken Wings x2
Formula: =HLOOKUP(“*Beef Brisket*”,Work!F2:2,1,false)
This says, “Search the entire second row on the ‘Work’ sheet starting at F2 for a cell that contains ‘Beef Brisket’. It’s OK if there are characters before or after it. Output that cell. The range is not sorted.”
Output: PBM-055 Braised BBQ Beef Brisket x5
Part 2: Extract the Last Value in the Cell
This is the answer for which I’d been looking. I needed the number at the end of the cell to determine the count on the individual product. So I used RIGHT which returns the string at the end of a cell.
Input: PBM-055 Braised BBQ Beef Brisket x5
Formula: =RIGHT(F2, 1)
This says, “Take cell F2 and return 1 character from the end.”
Output: 5
Final Formula
I put a couple more tweaks in to get it the way I wanted. If the particular product wasn’t ordered by the customer and wasn’t in the input cell then the formula would return an error. I wanted it to stay blank, so I used IFERROR.
I also needed it to copy correctly when I dragged it to adjacent cells, so I added a dollar sign so the column wouldn’t change.
Input: PBG-012 Machaca x1 PBM-055 Braised BBQ Beef Brisket x5 PCM-142 Lemon Pepper Chicken Wings x2
Formula: =IFERROR(RIGHT(HLOOKUP(“*Beef Brisket*”,Work!$F2:2,1,false),1))
This says, “Search the entire second row on the ‘Work’ sheet starting at F2 for a cell that contains ‘Beef Brisket’. It’s OK if there are characters before or after it. Output that cell. The range is not sorted.” Then it says, “Take cell F2 and return 1 character from the end.” Finally the formula says, “If you can’t find ‘Beef Brisket’ in the range, don’t return an error message. Instead return a blank cell.”
Output: 5
How to get a Conditional Formatting Custom Formula to Reference Another Sheet in Google Sheets – INDIRECT
I decided to get fancy and indicate which customers were “We Picks” by using a conditional format. I had problems getting the formula to reference a cell on another sheet but finally found a solution in the forums.
Select a range then Format > Conditional Formatting > Custom
=INDIRECT(“Work!F2:F200″)=”WE PICK”
Conclusion
This was the hardest project I’ve tackled but I learned a lot.
Leave a Reply