Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

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

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

Excel Video Tutorials / Excel Dashboards Reports

2. Excel Samurai
Join Date
19th July 2004
Location
Tokyo, Japan
Posts
9,423

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

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

Excel Video Tutorials / Excel Dashboards Reports

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

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