Announcement

Collapse
No announcement yet.

Unconfigured Ad Widget

Collapse

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

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

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

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

    One way...
    Code:
    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

    Comment


    • #3
      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

      Comment


      • #4
        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.

        Comment


        • #5
          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

          Comment

          Trending

          Collapse

          There are no results that meet this criteria.

          Working...
          X