News, tips, advice, support for Windows, Office, PCs & more. Tech help. No bull. We're community supported by donations from our Plus Members, and proud of it
Home icon Home icon Home icon Email icon RSS icon
  • Easy way to truncate a cell

    Posted on beethoven Comment on the AskWoody Lounge
    Topic Resolution: Resolved
    Viewing 5 reply threads
    • Author
      Posts
      • #2291717 Reply
        beethoven
        AskWoody Plus

        Each week I am working on spreadsheet with one cell filled like this:

        #1961 XL ZIPPY JACKET  #J21103 FLOWER BASKET NECKLACE SOLD OUT – REFUND TO FOLLOW  SINGING WRENS NO LONGER AVAILABLE – FREE SUBSTITUTE GIFT INCLUDED

        I need to put my cursor after the last valid item name (in this case Necklace) and delete the rest of the cell.  This is then used for a mail merge, so basically I need to remove redundant text.

        Is there any easy way to achieve this?  Any command to say “delete anything in this cell to the right of my cursor” ?   And thinking ahead perhaps  I could train everyone else to put a key symbol after the last item when entering the above, could I  then even use a function like replace all text after a given symbol with empty space within the cells of one column?

        And additionally how would I convert all text in a cell to Capital Letters

         

        • This topic was modified 3 weeks, 3 days ago by beethoven.
      • #2291742 Reply
        Kirsty
        Da Boss

        how would I convert all text in a cell to Capital Letters

        That one is easier… use the formula “=UPPER(cell ref)” in another column. So if you used column A for the entry to be changed, in a spare/blank column (ie B), you would use that entry. So in B1, you’d use “=UPPER(A1)”. You then hide column A, in this example.

        To change to Initial Caps only, use PROPER instead of UPPER, and to change to LowerCase, use LOWER 🙂

      • #2291749 Reply
        Paul T
        AskWoody MVP

        The hard bit is determining where to trim the text. If it’s always at “SOLD OUT” you can search for that term and trim there.

        =MID(A1,1,FIND("SOLD OUT",UPPER(A1))-1)

        This will convert the text in A1 to upper case, search for SOLD OUT, then return the string up to SOLD OUT.

        cheers, Paul

        • This reply was modified 3 weeks, 3 days ago by Paul T.
        1 user thanked author for this post.
      • #2291752 Reply
        anonymous
        Guest

        beethoven wrote:
        I could train everyone else to put a key symbol after the last item when entering the above, could I then even use a function like replace all text after a given symbol with empty space within the cells of one column?

        To delete cell content after inserted symbol, maybe something like this…

        for inserted symbol ~ (single tilde), with data in column D,
        formula: =LEFT(D2,FIND(“~”,D2)-1)

        or for inserted symbols !! (double bang), with data in column D,
        formula: =LEFT(D2,FIND(“!!”,D2)-1)

        Hope this helps.

        1 user thanked author for this post.
      • #2291762 Reply
        zeddy
        AskWoody_MVP

        As Paul says, the hard part is where to trim the text. You would need to choose a symbol that wouldn’t be used elsewhere in the cell contents. Perhaps you could get the User to place say, two exclamations at the point you want, as our anonymous guest suggests

        (and beat me to it)

        But to avoid errors where no trimming is required, I would use

        =IFERROR(MID(A2,1,FIND(“!!”,UPPER(A2))-1),UPPER(A2))

        ..as per the attached example file

        zeddy

        zeddy-sample

        Attachments:
        1 user thanked author for this post.
      • #2291768 Reply
        beethoven
        AskWoody Plus

        Excellent – both initial suggestions work like a charm. So instead of having to modify each cell, I can apply the formula once and the last suggestion re avoiding errors where no trimming is necessary is even better (though a bit harder for me to understand) . All this will save a lot of time.   I really appreciate your help.

        • This reply was modified 3 weeks, 3 days ago by beethoven.
        • #2291776 Reply
          Paul T
          AskWoody MVP

          zeddy uses an IFERROR to see if the text is found. If so it is trimmed, if not the text is used as is.
          This way you only need to look in the helper cell for the correct information.

          cheers, Paul

          1 user thanked author for this post.
        • #2296865 Reply
          beethoven
          Guest

          Hi Zeddy,

          I am trying to go all in and use the final formula with error correction but the final formula resists. Retyping it very carefully and only exchange A2 to P2 as the relevant cell and using “~” instead of “!!” excel tells me the formula is wrong (too many arguments for this function). What is the purpose of the 1 before Find and is that a constant?

    Viewing 5 reply threads

    Please follow the -Lounge Rules- no personal attacks, no swearing, and politics/religion are relegated to the Rants forum.

    Reply To: Easy way to truncate a cell

    You can use BBCodes to format your content.
    Your account can't use Advanced BBCodes, they will be stripped before saving.