Splitting the contents of a cell

**sheargraphix** · 12-08-2006, 01:47 PM

Hi guys, im looking for a little help with a problem i have here. Im importing data into a spreadsheet and in each cell in one collum there are two pieces of data that need to be split.

The cell length is 17 characters long, with the firts piece of data being 6 digits and the second set of data being 11.

What i want to do is to run a macro that will take my selection of cells and split them into 2 seperate cells. So if A1 to A10 would be split into B1 to B10 for the first 6 digits and C1 to C10 for the second set of digits (11 characters long).

Is it possible?

My other question is regrading if statements. what i want to do is as follows:

If cell selection = 1 then cell selection should now show as "mark"
If cell selection = 2 then cell selection should now show as "Dave"

again is this possible? Thanks alot for the help, its already helped me do so much

**VBA Noob** · 12-08-2006, 01:50 PM

Hi,

Does it have to be VBA code.

You can use formulas for all this

VBA Noob

**VBA Noob** · 12-08-2006, 01:58 PM

Hi,

Formula's for splitting the cells

First 7
=LEFT(A1,7)

Last 7
=RIGHT(A1,11)

Some code to change a say a ref eg B3 to Mark. It's an event Macro so right click the sheet your using and select view code and then paste

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$3" Then
Range("B3").Value = "Mark"
End If
End Sub

VBA Noob

**sheargraphix** · 12-08-2006, 03:09 PM

Thanks for the help, ive modified the code slightley and its working great. However one thing id like to know is if its possible to remove a digit from right hand side of a cell on only one collum.

For example im highlighting all the cells in the workbook and running a macro where i want all the cells in collum A to have a digit removed at the right hand side of there cell. I.e 3001 becomes 300.

Is it possible to make this collum specific using VB?

**VBA Noob** · 12-08-2006, 03:35 PM

Hi,

Try this code

Sub delete_last()
Application.ScreenUpdating = False
Dim rng As Range
Dim cell As Range
Set rng = Selection
For Each cell In rng
    If Len(cell.Value > 1) Then
        cell.Value = Left(cell.Value, Len(cell.Value) - 1)
    End If
Next
Application.ScreenUpdating = True
End Sub

VBA Noob

**sadhuhema** · 04-14-2011, 07:11 AM

Hi,

In A1 I have cell data like this "h1/h2/h3/h1" from this i need to takeoff the unique values and need to display "h2/h3" in A2.

Any clues. Thanks in advance.

HS

Splitting the contents of a cell

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Splitting the contents of a cell

Re: Splitting the contents of a cell

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions