+ Reply to Thread
Results 1 to 10 of 10

Formula to move part of a value to a new cell - part 2

  1. 04-28-2016, 11:24 AM #1
    forestavekids
    forestavekids is offline
    Forum Contributor
    Join Date
    10-10-2015
    Location
    nj
    MS-Off Ver
    Office 2021
    Posts
    203

    Formula to move part of a value to a new cell - part 2

    Hi guys,

    Having major issues with my skus...

    I need part of a sku code moved to a new cell.. example..

    Cell A1: 7611131MY-size-2t_8641_12671_b54f9586539

    need everything before the word "size" moved to cell B2- 7611131MY-

    Thanks again!
    Reply With QuoteRegister To Reply
  2. 04-28-2016, 11:29 AM #2
    Pete_UK
    Pete_UK is offline
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,704

    Re: Formula to move part of a value to a new cell - part 2

    A formula won't be able to remove that text from cell A1, but you can copy it into cell B2 with this formula:

    =LEFT(A1,SEARCH("size",A1)-1)

    Hope this helps.

    Pete
    Reply With QuoteRegister To Reply
  3. 04-28-2016, 11:31 AM #3
    oeldere
    oeldere is offline
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Formula to move part of a value to a new cell - part 2

    =LEFT(A1,SEARCH("size",A1,1)-1)
    Notice my main language is not English.

    I appreciate it, if you reply on my solution.

    If you are satisfied with the solution, please mark the question solved.

    You can add reputation by clicking on the star * add reputation.
    Reply With QuoteRegister To Reply
  4. 04-28-2016, 11:32 AM #4
    FoxSeaLady
    FoxSeaLady is offline
    Forum Contributor
    Join Date
    01-09-2016
    Location
    Perth Western Australia
    MS-Off Ver
    Office 365
    Posts
    257

    Re: Formula to move part of a value to a new cell - part 2

    Hi

    Try =left(A1,10). This assumes always first 10 characters are required.

    Cheers
    Reply With QuoteRegister To Reply
  5. 04-28-2016, 11:50 AM #5
    forestavekids
    forestavekids is offline
    Forum Contributor
    Join Date
    10-10-2015
    Location
    nj
    MS-Off Ver
    Office 2021
    Posts
    203

    Re: Formula to move part of a value to a new cell - part 2

    Hi guys, they all worked thank you so much.

    FoxSeaLady, you made it pretty simple to understand, I wish I could figure out how Pete or Oeldere can whip up those formulas!

    To keep it simple like FoxSeaLady, if I wanted everything AFTER "size" what would be the formula?
    Reply With QuoteRegister To Reply
  6. 04-28-2016, 11:57 AM #6
    AlKey
    AlKey is offline
    Forum Guru AlKey's Avatar
    Join Date
    07-20-2009
    Location
    Lakeland, FL USA
    MS-Off Ver
    Microsoft Office 2010/ Office 365
    Posts
    8,903

    Re: Formula to move part of a value to a new cell - part 2

    To get after size try this

    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    If you like my answer please click on * Add Reputation
    Don't forget to mark threads as "Solved" if your problem has been resolved

    "Nothing is so firmly believed as what we least know."
    --Michel de Montaigne
    Reply With QuoteRegister To Reply
  7. 04-28-2016, 11:58 AM #7
    forestavekids
    forestavekids is offline
    Forum Contributor
    Join Date
    10-10-2015
    Location
    nj
    MS-Off Ver
    Office 2021
    Posts
    203

    Re: Formula to move part of a value to a new cell - part 2

    Hi guys, they all worked thank you so much.

    FoxSeaLady, you made it pretty simple to understand, I wish I could figure out how Pete or Oeldere can whip up those formulas!

    To keep it simple like FoxSeaLady, if I wanted everything AFTER "size" what would be the formula?
    Reply With QuoteRegister To Reply
  8. 04-28-2016, 12:07 PM #8
    Tony Valko
    Tony Valko is offline
    Forum Expert Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Formula to move part of a value to a new cell - part 2

    Quote Originally Posted by forestavekids View Post
    Cell A1: 7611131MY-size-2t_8641_12671_b54f9586539

    if I wanted everything AFTER "size" what would be the formula?
    I assume you don't want the dash -...

    =MID(A1,SEARCH("size",A1)+5,50)
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.
    Reply With QuoteRegister To Reply
  9. 04-28-2016, 12:29 PM #9
    oeldere
    oeldere is offline
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: Formula to move part of a value to a new cell - part 2

    a1= 7611131MY-size-2t_8641_12671_b54f9586539
    b1=7611131MY-
    c1 =RIGHT(A1,LEN(A1)-LEN(B1))

    result
    size-2t_8641_12671_b54f9586539
    Reply With QuoteRegister To Reply
  10. 04-28-2016, 12:34 PM #10
    Pete_UK
    Pete_UK is offline
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Office 2019 (still learning)
    Posts
    24,704

    Re: Formula to move part of a value to a new cell - part 2

    Another one for C1, just for the variety:

    =SUBSTITUTE(A1,B1&"size-","")

    Hope this helps.

    Pete
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Formula to move part of a value to a new cell
    By forestavekids in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 04-12-2016, 10:42 AM
  2. Trying to retain data in one part of my worksheet which comes from formula in another part
    By Murray Field in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-03-2016, 11:48 PM
  3. Find Part number through Macro and output to associated part to certain cell
    By boylers75 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-09-2012, 04:32 PM
  4. move part of cell data
    By vermicelli in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-30-2007, 04:48 PM
  5. move part of a cell content
    By slowhand in forum Excel General
    Replies: 2
    Last Post: 04-14-2006, 11:20 AM
  6. Move Part of Cell String
    By tom in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 10-13-2005, 02:05 PM
  7. move part of cell to new column
    By S.E. in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 05-03-2005, 12:06 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1