function left on another spreadsheet

**fokz8** · 03-27-2016, 06:25 AM

Hello guys. I'm a big new at excel since i've only used it to store data, never used any functions.

I would be really happy if you guys could help me create a function which would:

- limit the length of current cell (SKU) to 59 characters
- get data from two different cells - category (e) and manufacturer (f)
- remove characters such as + &. /, ® ()% O ™ * (using regexreplace)
- remove spaces from the product names replaced by dashes using the function SUBSTITUTE.

All the data is stored on Sheet2 and i want it in my Sheet1.

The result should look like:

A1 - SKU
A2 CLOTHES-UNDERARMOUR

Any help will be appreciated!

**JohnTopley** · 03-27-2016, 06:48 AM

Please post a small sample file (not image) with examples of expected output.

To upload a file, click "Go Advanced" then "Manage Attachments"

**fokz8** · 03-27-2016, 06:57 AM

The manage attachements doesn't seem to be working or i may be missing some module.

Anyway here's the dropbox link of the sample

https://www.dropbox.com/s/rfle1x4767...mple.xlsx?dl=0

To sum up:
- i need to get data from Sheet2 to Sheet1
- the data needs to be in 1 cell instead of two
- functions needs to remove all of those characters (+&./,®()%™O*)
- any spaces needs to be substituted by "-"
- the max length of SKU cell must be 59 characters.

Thank you!

**Pete_UK** · 03-27-2016, 07:27 AM

You can use this formula in A2 on Sheet1:

=LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(List2!A2&"-"&List2!B2," ","-"),"+",""),"&",""),"/",""),"(",""),")",""),",",""),"%",""),"®",""),"™",""),"*",""),59)

Hope this helps.

Pete

**fokz8** · 03-27-2016, 07:31 AM

Originally Posted by Pete_UK

You can use this formula in A2 on Sheet1:

=LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(List2!A2&"-"&List2!B2," ","-"),"+",""),"&",""),"/",""),"(",""),")",""),",",""),"%",""),"®",""),"™",""),"*",""),59)

Hope this helps.

Pete

Excellent! Needed to adjust List2 as Sheet2 and cells as "E" and "F", but your code works perfect! Thank you very much kind sir!

function left on another spreadsheet

LinkBack

Thread Tools

Rate This Thread

Display

function left on another spreadsheet

Re: function left on another spreadsheet

Re: function left on another spreadsheet

Re: function left on another spreadsheet

Re: function left on another spreadsheet

Thread Information

Users Browsing this Thread

Similar Threads

Bottom left of spreadsheet shows that its Calculating Cells....

Spreadsheet shifts to the left when ENTER key is pressed

[SOLVED] IF Function referencing IsNumber, Match, Left function on separate sheets

Quick hide bar on left side of spreadsheet/workbook

[SOLVED] Can't see row numbers or left scroll arrow in Excel spreadsheet

[SOLVED] HOW DO I NEST THE VLOOKUP FUNCTION WITH THE LEFT FUNCTION

+/- signs on left of spreadsheet

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions