+ Reply to Thread
Results 1 to 5 of 5

function left on another spreadsheet

  1. #1
    Registered User
    Join Date
    03-27-2016
    Location
    Slovenia
    MS-Off Ver
    2016
    Posts
    9

    function left on another spreadsheet

    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!
    Last edited by fokz8; 03-27-2016 at 06:29 AM.

  2. #2
    Forum Expert
    Join Date
    05-05-2015
    Location
    Waterlooville,England
    MS-Off Ver
    Office 2010
    Posts
    20,252

    Re: function left on another spreadsheet

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

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

  3. #3
    Registered User
    Join Date
    03-27-2016
    Location
    Slovenia
    MS-Off Ver
    2016
    Posts
    9

    Re: function left on another spreadsheet

    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!

  4. #4
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    21,682

    Re: function left on another spreadsheet

    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

  5. #5
    Registered User
    Join Date
    03-27-2016
    Location
    Slovenia
    MS-Off Ver
    2016
    Posts
    9

    Re: function left on another spreadsheet

    Quote Originally Posted by Pete_UK View Post
    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!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Bottom left of spreadsheet shows that its Calculating Cells....
    By blackscholes in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-26-2014, 11:49 AM
  2. Spreadsheet shifts to the left when ENTER key is pressed
    By HitTheEXCELerator in forum Excel General
    Replies: 2
    Last Post: 02-28-2014, 01:04 AM
  3. [SOLVED] IF Function referencing IsNumber, Match, Left function on separate sheets
    By Touch9713 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-12-2013, 10:09 PM
  4. [SOLVED] Quick hide bar on left side of spreadsheet/workbook
    By TheTaxClub in forum Excel General
    Replies: 3
    Last Post: 06-27-2006, 01:10 PM
  5. Can't see row numbers or left scroll arrow in Excel spreadsheet
    By Jim Freund in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 01-26-2006, 12:10 PM
  6. [SOLVED] HOW DO I NEST THE VLOOKUP FUNCTION WITH THE LEFT FUNCTION
    By CHAIM in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 PM
  7. +/- signs on left of spreadsheet
    By raehippychick in forum Excel General
    Replies: 2
    Last Post: 04-05-2005, 08:39 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1