Announcement

Collapse
No announcement yet.

Deleting second line of text in a wrapped cell

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Deleting second line of text in a wrapped cell



    Hi all,

    In cell A1 I have:

    name lastname
    address

    OR

    name lastname
    address zipcode

    wrapped into 2 lines. I need vb code for excel 2011 to delete the second line from the cell and just keep the first line.

    Thank you in advance.

  • #2
    Re: Deleting second line of text in a wrapped cell

    xldummy,

    Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

    1. Copy the below code, by highlighting the code and pressing the keys CTRL + C
    2. Open your workbook
    3. Press the keys ALT + F11 to open the Visual Basic Editor
    4. Press the keys ALT + I to activate the Insert menu
    5. Press M to insert a Standard Module
    6. Where the cursor is flashing, paste the code by pressing the keys CTRL + V
    7. Press the keys ALT + Q to exit the Editor, and return to Excel
    8. To run the macro from Excel, open the workbook, and press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

    Code:
    Option Explicit
    Sub DeleteSecondTextLine()
    ' Stanley D. Grom, 10/28/2012
    ' http://www.ozgrid.com/forum/showthread.php?t=171305
    Dim c As Range, s
    Application.ScreenUpdating = False
    For Each c In Range("A1", Range("A" & Rows.Count).End(xlUp))
      If InStr(c, Chr(10)) > 0 Then
        s = Split(c, Chr(10))
        c = s(0)
      End If
    Next c
    Application.ScreenUpdating = True
    End Sub
    Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

    Then run the DeleteSecondTextLine macro.

    Have a great day,
    Stan

    Comment


    • #3
      Re: Deleting second line of text in a wrapped cell

      Hi Stan,

      Thank you so much for your prompt reply.

      I used your macro but nothing happened. Then I tried changing Chr(10) to Chr(13) and worked fine.

      But I'm still in big trouble ! (Please see my attached workbook - Bank.xls)

      The problem is:
      I download 2 xls sheets from the bank and have to compare col A on sheet1 to col B on sheet 2. Although the names in both sheets refer to the same articles (bonds) however they are written in a different way.
      I tried your maco and to go from "original" to "after macro" on sheet 1. However as you can see, I still have some records that don't match to sheet2.

      I've been working on this problem for 4 days now and don't know what to do.
      Is there anyway I can do this or should I just give up ?

      Thanks again for any help.
      Attached Files

      Comment


      • #4
        Re: Deleting second line of text in a wrapped cell

        xldummy,

        I would have to see more of the actual raw data in Sheet1 and Sheet2 in the actual cells, rows, and columns.

        Have a great day,
        Stan

        Comment


        • #5
          Re: Deleting second line of text in a wrapped cell

          Stan,

          I'm attaching another (simplified) workbook for you (Investments.xls). Please don't worry about rows and columns, I'll take care of those.
          Let's start with sheet "Transactions", it tells you what we bought. Now we take "Name" , "Quantity", "Price Paid" from this sheet and we paste them onto sheet called "My Investments".
          Then, in order to know the performance of our investments we have to look up the "market Value" of what we have in the sheet called "Today" and write it in the column to calculate Gain/Loss.

          Now I',m stuck ! How do I make it if the "Names" in col C of sheet "Transactions" and "Names" in col. A of sheet "Today" are written in different way ? I know they refer to the same thing but can't compare them if written differently.

          Tkx Stan, hope I don't bug you too much.
          Wish you a great day also.
          Attached Files

          Comment


          • #6
            Re: Deleting second line of text in a wrapped cell

            xldummy,


            Detach/open workbook UpdateInvestments in My_Investments colA in InStr wToday colA if qty the same copy MarketValue - xldummy - OZ171305 - SDG17.xls and run the UpdateInvestments macro.


            Let me know what the formulae should be for worksheet My Investments column E.


            Have a great day,
            Stan
            Attached Files

            Comment


            • #7
              Re: Deleting second line of text in a wrapped cell

              Stan,
              Thank you for your help .
              Your workbook and it's macro take me exactly to the point where I'm stuck.

              Now, for each article (Name) in col A of " My Investments" sheet I have to look up it's corresponding "Market Value" In "Today" sheet. (Pls note items could be listed in different order in each sheet). I then should write the market values of all items in col D of "My Investments". Col E is easy, its just a subtraction of the market value of each item from its cost so we have our Gain/Loss.
              The Problem is How do you compare /lookup market values if items are written differently in two sheets ? Using your macro from the first post, I tried deleting the second line of the names in col A of "Today" sheet (Pls see Bank.xls attached to my second post) but still can't get the names to match.

              Thank you again for your patience and help.

              Comment


              • #8
                Re: Deleting second line of text in a wrapped cell

                xldummy,

                The macro in my attached workbook in Reply #6, does everything you have asked for. I have updated the macro in the current version (which you have not seen yet) to include the formulae in worksheet My Investments column E.

                I'm attaching another (simplified) workbook for you (Investments.xls). Please don't worry about rows and columns, I'll take care of those.
                To continue, please attach another workbook containing the four worksheets, with all the actual raw data/titles in their actual worksheets, cells, rows, and columns.

                If you can not attach what I have asked for:

                In the Quick Reply box, just put the word BUMP. Then, click on the Post Quick Reply button, and someone else will assist you.

                Have a great day,
                Stan

                Comment


                • #9
                  Re: Deleting second line of text in a wrapped cell

                  Stan,
                  You are my hero !!!
                  I just changed Chr(10) to Chr(13) and all works.
                  I just wish I could find the right words to thank you. You saved my sanity.
                  God bless you.
                  p.s. If you ever come to Italy just let me know. I owe you dinner and wine

                  Comment


                  • #10


                    Re: Deleting second line of text in a wrapped cell

                    xldummy,

                    Thanks for the feedback.

                    You are very welcome. Glad I could help.

                    Come back anytime.

                    When we get to Italy, pasta and vino sound great....

                    Comment

                    Working...
                    X