Learn More
Request a Demo
    September 9, 2013
    19 minute read

    Curating Product Content with Microsoft Excel

    by: Steve Johnston

    Screen_Shot_2014-04-20_at_9.52.05_PM

    Update: we've made the cheat sheet available as a PDF. Click here to download.

    While I don’t consider myself a perfectionist, I do get annoyed when product content isn’t formatted consistently. For example, some products I see on Amazon have PRODUCT NAMES IN ALL CAPS while others have the product number inserted at the end of the product title 485302456. If you thought the errors in the previous sentence were irritating, you know what I mean. I just think that the lack of consistency hurts the shopping experience – and anything that hurts the shopping experience hurts the brand that I am shopping for.

    This doesn’t happen only on Amazon; this is a challenge for all e-commerce retailers AND the manufacturers that supply them with product content. On the retailer side, it is difficult to standardize all manufacturers to your specific requirements. On the manufacturer side, it is equally challenging to support all their retail partners with individualized templates. Add in the complexity of compiling content from six, seven, and sometimes eight separate teams, and the challenge compounds exponentially. The consequences are 1) manufacturers run through fire drills trying to supply accurate and timely data 2) retailers manually reformatting product content. The end results are a lot of headaches for all involved and sub-par shopping experiences for end consumers.

    Feeling inspired by this opportunity, I sought to compile the Microsoft Excel functions that are useful in curating product content. This cheat sheet should be helpful for anyone curating product content with Microsoft Excel.

    Excel Function Cheat Sheet for Curating Product Content

    style='width:482.25pt;margin-left:4.65pt;border-collapse:collapse;mso-yfti-tbllook:
    1184;mso-padding-alt:0in 5.4pt 0in 5.4pt'>

    Excel Function

    Microsoft Excel Description

    Use Case

    Formatting Numbers

     

     

     

     

    =CONVERT

    Converts a number from one measurement system
    to another

    Very helpful when customizing data for your
    retail partners, especially those that are international

     

    =FIXED

    Rounds a number to the specified number of
    decimals and returns the result as text with our without commas

    Easily normalize your data using this function
    for consistent presentation

    Formatting Text

     

     

     

     

    =CLEAN

    Removes all unprintable characters from text

    Unprintable characters can pop up when data is
    being moved around to/from different systems

     

    =CONCATENATE

    Joins several text strings into one text
    string

    Great for combining columns of information to
    meet the formatting requirements of your retail partners

     

    =LOWER

    Converts all text characters to lower case

    LOWER, PROPER, and UPPER are important
    functions to ensure a uniform presentation of data

     

    =PROPER

    Converts a text string to proper case; the
    first letter in each word in uppercase; all other letters in lower case

     

    =UPPER

    Converts all text characters to upper case

     

    =SUBSTITUTE

    Replaces existing text with new text in a text
    string

    A quick method for bulk editing

     

    =TEXT

    Converts a value to text in a specific number
    format

    For when you need to normalize the format of
    product codes and other numbers; very helpful for when you need your numbers
    includes leading zeros

     

    =TRANSPOSE

    Converts a vertical range of cells to
    horizontal, or vice versa

    Instead of endlessly cutting and pasting, use
    this function to quickly reformat your data

     

    =TRIM

    Removes all spaces from a text string except
    for single spaces between words

    An easy and effective way to normalize your
    data

    Character Counts

     

     

     

     

    =LEN

    Returns the number of characters in a text
    string

    These functions are very helpful when you are
    trying to split a single column in to separate fields (e.g. moving Length x
    Width dimensions from one combined column into separate fields

     

    =LEFT

    Returns the specified number of characters
    from the start of the text string

     

    =MID

    Returns the characters from the middle of the
    text string, given a starting position and length

     

    =RIGHT

    Returns the specified number of characters
    from the end of the text string

    Quality Control

     

     

     

     

    =COUNTBLANK

    Counts the number of empty cells in a
    specified range of cells

    Quickly check to make sure there's no missing
    information

     

    =EXACT

    Checks whether two text strings are exactly
    the same, and returns TRUE or FALSE (case sensitive)

    Confirm duplication of long numbers or written
    copy

    By no means is this an exhaustive list, and are there a million excel tricks beyond utilizing just the built-in functions, but I hope this helps to both manufacturers and retailers in getting your data in tip top shape.

    New call-to-action

     

    Related Posts

    Keep Reading

    May 6, 2014

    3 Reasons Why Product Content Exchange Should Be Your #1 Priority

    5 minute read
    As e-retailers plan their investment strategies and focus on mobile, personalization, omnichannel, etc., the retail community is putting the cart before the horse. These...
    by: Steve Johnston
    June 1, 2016

    Introducing our Expanded Platform for Product Content Management

    3 minute read
    Today we’re celebrating the launch of our unified product content management (PCM) platform. Salsify users now have an efficient way to create, manage, optimize, and...
    by: Josh Mendelsohn
    April 19, 2017

    How Millennials Think About Your Product Content

    8 minute read
    Takeaway: I'm a millennial who keeps an eye on the latest whole food and health food trends. Here are 4 ways that food brands and retailers can catch and keep my...
    by: Fiona Galey