+ Reply to Thread
Results 1 to 6 of 6

Cutting names and dividing

  1. #1
    Registered User
    Join Date
    01-23-2005
    Posts
    20

    Exclamation Cutting names and dividing

    First, thanks to all who help me out. OK, here's the problem.

    I have a worksheet of names, addresses, and other info. A person's name is in the following format in a single cell A1:
    _][___A______]
    1][Doe, John Q.]

    I want this name to be divided into three seperate cells to read
    _][__A__][_B_][_C_]
    1][ John ][ Q. ][ Doe ]

    I should also mention that not all the names include all parts and say just:
    _][___A____]
    1][Doe, John]
    or
    _][_A_]
    1][Doe]

    I already saw the functions that "extract" the first, middle and last names. But, they require the original cell to remain. I need a function that will do what I would have to do for days using cut and paste to truly divide the data into 3 columns, not just return its value in three columns. Are there any options here? If functions won't do it, what other choices have I?
    Last edited by TheOne; 01-23-2005 at 04:29 PM.

  2. #2
    Forum Contributor
    Join Date
    11-09-2004
    Posts
    451
    You have to use macro for that.

  3. #3
    Registered User
    Join Date
    01-23-2005
    Posts
    20

    How do I do that

    Ok, how do I do that then?

  4. #4
    Forum Contributor
    Join Date
    11-09-2004
    Posts
    451
    Try this macro

    Sub macro()
    Dim v, t As Variant
    Range("a1").Select 'selection cell that has to be split into three
    v = Range("a1").Value
    t = Split(v, " ") 'split the cell value by three
    If UBound(t) = 2 Then ' checking if number of spaces =2
    Range("a1").Value = t(0)
    t1 = Split(t(0), ",")
    If UBound(t1) > 0 Then
    Range("a1").Value = t1(0)
    End If
    Range("b1").Value = t(1)
    Range("c1").Value = t(2)
    ElseIf UBound(t) = 1 Then ' checking if number of spaces =1
    Range("a1").Value = t(0)
    Range("c1").Value = t(1)
    ElseIf UBound(t) = 0 Then 'if no spaces available
    Range("a1").Value = v
    End If
    End Sub

  5. #5
    Registered User
    Join Date
    01-23-2005
    Posts
    20
    Hey, that worked well. But, I am new to macros. So, how do I apply it to the whole column, actually going from D39:857?

  6. #6
    Forum Contributor
    Join Date
    11-09-2004
    Posts
    451
    I considering the range to be d39:d57

    Sub macro()
    Dim v, t As Variant
    Dim r As Range

    Range("D39:d57").Select 'selection cell that has to be split into three
    Set r = Selection
    For Each c In r
    Range(c.Address).Select
    v = Range(c.Address).Value
    t = Split(v, " ") 'split the cell value by three
    If UBound(t) = 2 Then ' checking if number of spaces =2
    Range(c.Address).Value = t(0)
    t1 = Split(t(0), ",")
    If UBound(t1) > 0 Then
    ActiveCell.Value = t1(0)
    End If
    ActiveCell.Offset(0, 1).Value = t(1)
    ActiveCell.Offset(0, 2).Value = t(2)
    ElseIf UBound(t) = 1 Then ' checking if number of spaces =1
    ActiveCell.Value = t(0)
    ActiveCell.Offset(0, 2).Value = t(1)
    ElseIf UBound(t) = 0 Then 'if no spaces available
    ActiveCell.Value = v
    End If
    Next
    End Sub

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1