Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 5 of 5

Thread: Macro to split single cell Value Number and String in two cells

  1. #1
    Join Date
    14th November 2011
    Posts
    28

    Macro to split single cell Value Number and String in two cells

    Hello Experts,

    Looking for Macro to split singel cell Value Number and String in to Two different cells. Please look in the enclosed excel sheet.

    I want to split the number and text from the coloum A.
    For example in enclosed excel sheet
    If you see in

    cell A4 is (111 - Accumulated depreciation_)

    Output should be 111 in cell A4 and Accumulated depreciation should in cell B4

    we have in this first number “111” then “-” then “Accumulated depreciation_)” text with underscore.
    I want a macro which will copy or split or Delimt text into the Coloum B Description next to the number cell.

    Please Note I only want split the text with have “-“ not underscore in the number and text .

    I have very big excel sheet with repetiative same frequent number and text so I am looking to macro to automate this process.

    Test_Masson.xlsx
    Please reply.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    19th July 2004
    Location
    Tokyo, Japan
    Posts
    8,486

    Re: Macro to split singel cell Value Number and String in two cells

    One way...
    VB:
    Sub test() 
        Dim r As Range, x 
        For Each r In Range("a1", Range("a" & Rows.Count).End(xlUp)) 
            If r.Value Like "* - *" Then 
                x = Split(r.Value, "-", 2) 
                r.Value = Trim(x(0)) 
                r(, 2).Value = Trim(x(1)) 
            End If 
        Next 
    End Sub 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  3. #3
    Join Date
    25th January 2003
    Location
    UK
    Posts
    2,745

    Re: Macro to split singel cell Value Number and String in two cells

    Another way ...

    Could you not just use Data / Text To Columns and use the hyphen as the delimiter?
    Cross-poster? Read this: Cross-posters
    Struggling to use tags (including Code tags)? : Forum tags

  4. #4
    Join Date
    14th November 2011
    Posts
    28

    Re: Macro to split singel cell Value Number and String in two cells

    great this is working perfectly. Thank you so much for this quick reply.

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    6th September 2011
    Posts
    32

    Re: Macro to split single cell Value Number and String in two cells

    Hi All,
    I did try the above code for one of my requirement. But that didnt work. I have attached sheet which is having 4 columns and column B & D contains multiple values in a single cell. i want to split the multi values into individual cells for those columns. Attached is just sample with 1 row data. Can you please look into it and help me to solve this.

    Regards,
    NM
    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


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Split single cell number into columns
    By simpsonc in forum EXCEL HELP
    Replies: 3
    Last Post: July 12th, 2011, 16:20
  2. Split text string into separate cells
    By Tsuani in forum EXCEL HELP
    Replies: 3
    Last Post: September 11th, 2010, 01:18
  3. Split Single Cell Object Into Multiple Cells
    By gurunath_s_r in forum EXCEL HELP
    Replies: 2
    Last Post: August 13th, 2008, 11:00
  4. Split Data In Single Cell To Other Cells
    By dbla921 in forum EXCEL HELP
    Replies: 2
    Last Post: August 8th, 2008, 01:46
  5. Replies: 5
    Last Post: October 5th, 2005, 04:29

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