I'm looking to run a few scripts to format data files differently.

Hello and welcome to ExpertHelp.com! My name is Mario V. and I'm going to do everything in my power to answer your question to your full satisfaction!

Before we get started, I want to remind you ExpertHelp.com is an independent professional support company. We are not associated with Microsoft nor are we paid to provide support on their behalf. However, we have helped hundreds of customers with similar questions and believe we can help you too. OK, let’s get started! :)

I’m available to chat now. Please let me know that you are ready by posting a response. I’ll leave my chat session open for the next 15 minutes or so and wait for your reply. If I don’t catch you this time, please respond with a few times (including your timezone) that work best for you and we can connect then.

Hi,

Thank you for your patience. So that I can help you, could you please describe your issue in detail? What is the source format and a desired resulting format? Do you have an excel file you can attach?

Thank you,

Mario

Hi,

Have you received my messages? As it turns out, I am scheduled to go off-line now. I would really appreciate the opportunity to personally make sure you are satisfied with your answer and if it's OK with you, I'd like to continue our conversation at 9:00 AM EST. As an alternative, if you are really pressed for time, I'd request you contact customer support at s**t@experthelp.com so we can have another Expert take over where I left off now. Rest assured, we will ensure we do what it takes to resolve your issue.

Thank you for your understanding.

Hi Ben,

Thank you for your response. Are you able to send me your spreadsheet to e**o@outlook.com?

Is the SKU always in format <text><2-digit-number><text>? If it is, you may use the following formulas:

1. Extracting numbers from text:

=SUMPRODUCT(MID(0&A1,LARGE(INDEX(ISNUMBER(--MID(A1,ROW($1:$25),1))* ROW($1:$25),0),ROW($1:$25))+1,1)*10^ROW($1:$25)/10)

2. Inserting dot between digits:

=LEFT(B1,1)&"."&RIGHT(B1,1)

I will be waiting for your response.

Hi Benjamin,

Have you received my email?

Mario

Notes from email support

Customer requirements

For the “Products1116” tab:

1) When I download my products into a CSV file, it also downloads all the of the images associated with that product, which is usually 8 images. It’s the “Image Src” column. need just the 4th image/link (the side profile shot) copied into each of the cells for that product. I did it manually for J2 through J15. For example, I need the link in cell J57 copy and pasted into cells J44 through J57. And then J61 copy and pasted into cells J58 through J71, and so on.

2) Any rows with blank cells in columns C through F can be deleted. Shopify just generated these rows for when the product has additional images.

3) I need the URL for each product in a column K “Product URL". Our URLs are based off of the shoe name, and so it should be just a concatenate. I gave an example in K2. The original file actually has the names with the “-“ in them to start, which I took out. 

4) in Column L “Article ID”, I need the info in column F “Variant SKU” without the size numbers. Example in L2.

For the “Returns” tab: (this is a messy file, so if some of this isn’t possible or super complicated, it’s fine)

1) Clear column D and F of anything but the SKUs. No “?” or “(pre-order)”. 

2) If there’s multiple items per row, such as D51 and F51, separate them into their own rows, copying the rest of the info from columns A, B and C.

3) Add “.” to columns where it’s necessary for columns E and G. These are shoe sizes, so while I need “75” to be “7.5”, I don’t need “10” to be “1.0”.

Last thing for the Products1116 tab, I also need a new column for color, so just take the text after the numbers in Column F “Variant SKU”, so for cell F2, "TLS6TERRA-S”, in the new column it’s “TERRA-S”

Solution

Solution has been implemented in an excel file and sent to the customer.

Additional requests

Email:

"I added the raw return file to New Returns. I also added a tad called “Returns example”. This tab is to just show you want I need done, but you should set the macros up for New Returns, which is the raw file we use, so I can just dump it in there in the future.

1) Column A is good.

2) Column B is good.

3) Column C is good.

4) Column D is good.

5) Add new column called Return Article ID with the information from Column D less the sizing numbers in the middle. You did this for the Product Feed.

6) Add new column Return Size with just the numbers from Column G with periods included. These are shoe sizes, and need to be formatted with periods accordingly. You did this for the Product Feed. If column G has anything other than a SKU, such as a “no” or “?”, just leave Exchange Article ID and Exchange Size blank.

7) Delete columns titled:

Received & Inspected
Restocked Item?
Share-A-Sale Voided
Note

Follow up
8) Add column Quantity Returned, and just put “1” in each cell

9) Add column Reason Returned. 

-If Return Size > Exchange Size then Column K = “too big”

-If Return Size < Exchange Size then Column K = “too small”

-If neither of those, just leave it blank"

Thank you for the information.  I am preparing a customized quote to solve your problem right now. Please stand by to receive my quote.

Dear Benjamin,
I have completed your customized quote.  Please take a moment to review it.  Click the “Accept Quote” button so we may continue resolving additional requests defined below. 

Hi Benjamin,

Have you received my message?