Helpful Excel Formulas

This post is a collection of formula’s I have used IRL. These formulas have helped me learn how to manage updates and automate the process to import images and data to my employers website.

How to Change an Image Name into a WebStyle:

*Note – this will only work with styles that include color codes and have the same item number for all options.

This function was used to change the image name into a WebStyle to group each style on it’s respective product page.

=LEFT(A2,12)&”S18”

My images for the brand CHACO all follow the same format:

  • CHC~J106446~CRP~S18.jpg

This equation gave me:

  • CHC~J106446~S18

Since all these images are of Spring 2018 products, I was able to delete all the characters after my second Tilda (~) and add the test S18 to indicate the product pages would be organized by Season Code.

I refreshed my excel equations with SpreadsheetO’s tutorial, “How to Use the String Functions: LEFT, RIGHT MID and LEN.”

How to Delete the Domain Name in a URL:

I used this equation to change data exported from a website and remove the domain name so I could save the data as a .csv and update the URLs so they do not take the reader to a 404 page.

=RIGHT(A2,LEN(A2)-25)

To update URLs you need headers “OLD URL” and “NEW URL”

My 301 redirect importer needs the http://domain.com in the URL to be omitted. For example, if the full URL was:

  • https://spreadsheeto.com/text-functions/#left-mid-right

I would need to remove 25 characters in the beginning of the cell to get the data I need:

  • /text-functions/#left-mid-right

If your URL is shorter or longer change the “25” in the equation to the number of characters to the right of the part of the cell you want to keep.

Once you have the equation you can apply to all cells, copy the data and Paste Special (Values) into a new column. This should be under the header “OLD URL” now you can edit the next column and define if this page is a 404 page the reader will go to the “NEW URL” instead. 

How to Manipulate Data from the Middle of a Cell:

*Note – this will only work if you change your values to match the characters in a given cell.

This function was used to remove the brand code and season code of a product page to get a starting point for the style as it was entered in the POS.

=MID(A2,5,8)

I need to omit the first four characters, and tell excel to start counting at the 5th character and keep counting for 8 characters, the example was:

  • OSP~10000094~S18

I only want the number in the middle, which is searchable in our point of sales system. When I apply the formula above it leaves me with the style number.

  • 10000094

How to Change the Case of a Cell:

This function was used to copy a product title from the vendors website and format it the way we need it on our dealer site. I’m going for proper here, but you can also format with upper and lower.


=proper(A2)
  • A Product Page

=upper(A2)
  • A PRODUCT PAGE

=lower(A2)
  • a product page

Simple and practical, thanks TeachExcel on YouTube, “How to make test cells all lower case or upper case in Excel.”


Now here is where it gets tricky, if I have a cell that needs sentence capitalization–where only the first letter at the beginning of the sentence will be capitalized–we need a heavier formula. 

Thanks to Extend Office, “How To Capitalize First Letter Only Or First Letter Of Each Word In Excel?

I found a formula for this:

=UPPER(LEFT(A2,1))&LOWER(RIGHT(A2,LEN(A2)-1))
  • A product page.

How to Transpose from a Column to a Row:

Some of the Vendors give access to pdf catalogs that I can copy into excel. It’s difficult to deal with the data because it is in one column and I need it to be in rows so I can format it with html before I import it.

Extended Office introduced me to an Offset formula that transposes the data from one column into one row. I will need to proof read the copied data before applying the code but it will allow me to compile the data and prepare it for import.

=OFFSET($A$1,COLUMNS($A1:A1)-1+(ROWS($1:1)-1)*5,0)

The full tutorial can be found on the extended office website in their article, “How to transpose/convert a single column to multiple columns in Excel?” 

How to Combine Multiple Cells into One Cell:

This function was used to combine multiple cells including parts of the product name to one cell I can import as the product page name. This formula would also work for adding a persons first middle and last name to one cell, if the data had each name in it’s own cell. 

=CONCATENATE(B2," ",C2," ",D2," ",E2)

After practicing the formula I was able to get the desired end result: 

  • Women’s Benton Springs™ Full Zip

Thanks to the DullesDistrict video on YouTube, “Using PROPER and CONCATENATE in Excel” I have the full product page title in one column ready for import. 

I could also use this method to combine all the data for my descriptions, while also adding necessary html, for example:

=CONCATENATE(A2,"
  • “,B2,”
  • “,C2,”
  • “,D2,”
  • “,E2,”

”)

Given this strategy, IF I have all the items that need to appear in a bulleted list in separate cells I can add html between the quotes in the formula, which will allow me to prepare the data for import.  

=CONCATENATE(A2,"

FEATURES

  • “,B2,”
  • “,C2,”
  • “,D2,”
  • “,E2,”

”)

Normally I would only include the first break in the HTML, I need to touch base with the developers and get them to fix the spacing between my header and the beginning of my bulleted list. 

How to Combine Multiple Cells to Make a list Separated by Pipes “|” :

*Note – this will only work with data that includes the same number of list items.  

This function was used to combine select cells and add a pipe separator in between “.jpg” file names so they can be imported to the site with a “.csv” spreadsheet file.

I’m using the formula from the Transpose tab here. Depending on how many list items I have I’m changing the “5” in the equation to that number. In my example, I am collecting data from each product and including a default and 3 other alt images. So I will change this integer to a “4”

=OFFSET($A$1,COLUMNS($A1:A1)-1+(ROWS($1:1)-1)*4,0)

I begin these projects by copying file names and pasting them into a spreadsheet. This will be a long list in a column. But I need the data for each product in rows.  So I can create one cell with all the images for that item separated by the pipe “|” symbol. 

My list of image names are in column A, and the Formula will be inserted in C1. I will copy the formula to the next four rows C-F. Then I will hover over the bottom right of cell F1 and drag the black cross curser down the list to F10 (or until you start seeing “0” in cells instead of the data). 

Once I have my data in rows instead of columns I will copy and Paste Special (Values) to apply the next formula. I paste the formula below in G12, and copy the formula down to account for each line of data. 

=C12&" | "&D12&" | "&E12&" | "&F12

Now I will copy the data in column G and paste special (values) to get the full list of items needed for my import.  

Example:

default-image.jpg | alt-image(a).jpg | alt-image(b).jpg | alt-image(c).jpg

I started with a list of all the file names in a different cell within column A. Now I’ve got the image list separated by pipes located in one cell. 

 

How to _____:

*Note – this will only work with ________.

This function was used to  _________.

=

___________________:

____________________.

Important Reminder

When working with formulas it is highly advised that once it gives you the data you need in it’s own cell you add it to a new column or document and make it independent of the source data. To do this you can:

  1. Highlight & copy your data
  2. Insert a new column (Or create a new document)
  3. Select the starting point for your data to copy to: example (A2)
  4. Right click, choose “Paste Special” & “Values” (If you create a new document this is not an option and “Text” is the next best thing).

Now your data is independent of the source data & the formula. This helps when you export your data to a .csv file for import purposes. This is a necessary step, because if your equation or source data is deleted you do not lose the data you formatted.

Posted in Data/Excel, Tutorials.
Loading Facebook Comments ...

Join the conversation

Your email address will not be published. Required fields are marked *