Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 10 of 10

Thread: Deleting second line of text in a wrapped cell

  1. #1
    Join Date
    25th January 2011
    Posts
    18

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    17th November 2005
    Location
    North East Pennsylvania, USA
    Posts
    694

    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.

    VB:
     
    Option Explicit 
    Sub DeleteSecondTextLine() 
         ' Stanley D. Grom, 10/28/2012
         ' [URL]http://www.ozgrid.com/forum/showthread.php?t=171305[/URL]
        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

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    25th January 2011
    Posts
    18

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    17th November 2005
    Location
    North East Pennsylvania, USA
    Posts
    694

    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

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    25th January 2011
    Posts
    18

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    17th November 2005
    Location
    North East Pennsylvania, USA
    Posts
    694

    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. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    25th January 2011
    Posts
    18

    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.

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    17th November 2005
    Location
    North East Pennsylvania, USA
    Posts
    694

    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

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    25th January 2011
    Posts
    18

    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

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    17th November 2005
    Location
    North East Pennsylvania, USA
    Posts
    694

    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....

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Deleting text from a (text+numbers) cell
    By N.Poole in forum EXCEL HELP
    Replies: 1
    Last Post: August 22nd, 2011, 04:38
  2. Replies: 4
    Last Post: June 21st, 2011, 23:37
  3. Wrapped Button Text on Dialog Sheet Is Truncated
    By Dave Hawley in forum Excel Formulas (No Questions)
    Replies: 0
    Last Post: April 11th, 2008, 15:12
  4. Splitting Wrapped Cell Into Separated Cells
    By AbuYehya in forum EXCEL HELP
    Replies: 8
    Last Post: February 20th, 2007, 01:05
  5. Get Text Length Of 1 Text Line When Cell Has 2 Lines
    By Christopheb in forum EXCEL HELP
    Replies: 1
    Last Post: November 8th, 2006, 01:08

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
  •  
porno