+ Reply to Thread
Results 1 to 11 of 11

I need the first 4 letters from a cell and i need to put a count at the end of each

  1. 06-19-2013, 04:04 PM #1
    nate02167
    nate02167 is offline
    Registered User
    Join Date
    06-18-2013
    Location
    canada
    MS-Off Ver
    Excel 365 (Mac)
    Posts
    26

    I need the first 4 letters from a cell and i need to put a count at the end of each

    I Need to pull the first 4 letters from a cell, whilst not including characters like "&" symbols and spaces, numbers in the middle or end of the 4 is acceptable

    and then i need them to be counted and the count displayed as such GGTF-1, GGTF-2 , .......... so on so on......

    and the count is only of the set of 4 letters that are the same, eg if the GGTF turned into anything else even GGTG then a new count should start


    please review my uploaded photo it will make it abundantly clear

    thank you

    i need this bad otherwise i must type 11,000 cells by hand!!
    Attached Images Attached Images
    Reply With QuoteRegister To Reply
  2. 06-19-2013, 04:40 PM #2
    oeldere
    oeldere is offline
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: I need the first 4 letters from a cell and i need to put a count at the end of each

    =left(d283,4) and drag down.

    After that make an pivot table of it.

    Anoter option would be using countif.
    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
  3. 06-19-2013, 04:41 PM #3
    nate02167
    nate02167 is offline
    Registered User
    Join Date
    06-18-2013
    Location
    canada
    MS-Off Ver
    Excel 365 (Mac)
    Posts
    26

    Re: I need the first 4 letters from a cell and i need to put a count at the end of each

    what is a pivot table?
    Reply With QuoteRegister To Reply
  4. 06-19-2013, 04:57 PM #4
    oeldere
    oeldere is offline
    Forum Expert
    Join Date
    05-30-2012
    Location
    The Netherlands
    MS-Off Ver
    Office 365
    Posts
    14,987

    Re: I need the first 4 letters from a cell and i need to put a count at the end of each

    Excel 2007 => insert => pivot table.

    But if you don't know this option, maybe (for now) stick on the countif formula.
    Reply With QuoteRegister To Reply
  5. 06-19-2013, 05:13 PM #5
    daddylonglegs
    daddylonglegs is offline
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: I need the first 4 letters from a cell and i need to put a count at the end of each

    assuming data starts at row 2 try this formula copied down

    =LEFT(E2,4)&TEXT(COUNTIF(E$2:E2,LEFT(E2,4)&"*"),"00")
    Audere est facere
    Reply With QuoteRegister To Reply
  6. 06-20-2013, 10:15 AM #6
    nate02167
    nate02167 is offline
    Registered User
    Join Date
    06-18-2013
    Location
    canada
    MS-Off Ver
    Excel 365 (Mac)
    Posts
    26

    Re: I need the first 4 letters from a cell and i need to put a count at the end of each

    its working except its not deleting the spaces that are present, plus i need a dash (-) before the ending number, but its so close

    Oh and it must not accept (&) symbols


    what do i change please please
    Last edited by nate02167; 06-20-2013 at 10:19 AM.
    Reply With QuoteRegister To Reply
  7. 06-20-2013, 01:59 PM #7
    daddylonglegs
    daddylonglegs is offline
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: I need the first 4 letters from a cell and i need to put a count at the end of each

    OK that makes it a little trickier - try this version

    =LEFT(SUBSTITUTE(SUBSTITUTE(E2," ",""),"&",""),4)&TEXT(SUMPRODUCT((LEFT(SUBSTITUTE(SUBSTITUTE(E$2:E2," ",""),"&",""),4)=LEFT(SUBSTITUTE(SUBSTITUTE(E2," ",""),"&",""),4))+0),"-00")
    Reply With QuoteRegister To Reply
  8. 06-20-2013, 02:13 PM #8
    nate02167
    nate02167 is offline
    Registered User
    Join Date
    06-18-2013
    Location
    canada
    MS-Off Ver
    Excel 365 (Mac)
    Posts
    26

    Re: I need the first 4 letters from a cell and i need to put a count at the end of each

    wow your good, its working about 95 percent now, its not removing , and -

    is that fixable? if not il just make the little changes manually later

    thanks tho i just completed 5,000 cells in 1 minute haha
    Reply With QuoteRegister To Reply
  9. 06-20-2013, 02:19 PM #9
    daddylonglegs
    daddylonglegs is offline
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: I need the first 4 letters from a cell and i need to put a count at the end of each

    SUBSTITUTE removes the unwanted characters but unfortunately you need to "nest" another SUBSTITUTE function for each character (and in 3 places) so to amend my version to ignore dashes amend like this

    =LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(E2," ",""),"&",""),"-",""),4)&TEXT(SUMPRODUCT((LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(E$2:E2," ",""),"&",""),"-",""),4)=LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(E2," ",""),"&",""),"-",""),4))+0),"-00")

    add more SUBSTITUTE functions as required to remove more characters
    Reply With QuoteRegister To Reply
  10. 06-20-2013, 02:21 PM #10
    nate02167
    nate02167 is offline
    Registered User
    Join Date
    06-18-2013
    Location
    canada
    MS-Off Ver
    Excel 365 (Mac)
    Posts
    26

    Re: I need the first 4 letters from a cell and i need to put a count at the end of each

    can you put the period in too i just dont wanna mess it up, im a newb :S


    please and then its perfect!!!
    Reply With QuoteRegister To Reply
  11. 06-20-2013, 02:27 PM #11
    daddylonglegs
    daddylonglegs is offline
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: I need the first 4 letters from a cell and i need to put a count at the end of each

    Try this version

    =LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(E2," ",""),"&",""),"-",""),".",""),4)&TEXT(SUMPRODUCT((LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(E$2:E2," ",""),"&",""),"-",""),".",""),4)=LEFT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(E2," ",""),"&",""),"-",""),".",""),4))+0),"-00")
    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)

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