Separating first and last names - vba

[lordriel]

I've seen several methods for separating names, or text, from one cell to two or more. However, none of them give a vba answer.

How would I code it, were it to be done when hitting a command button?

 

[UrVabsy]

I tried using this same function in OpenOffice but it is not working. It is not possible to use VBA in OpenOffice Calc?
Screen shot below
https://cdn.discordapp.com/attachments/416663310176616478/546938986153639943/unknown.png

 

[Michael M]

Did you try using the code posted by @Peter SS ??

 

[UrVabsy]

Did you try using the code posted by @Peter SS ??

Thanks for the reply.
I tried that too. But, as I suspected OO Calc do not have same inbuilt function as MS Excel
https://cdn.discordapp.com/attachments/416663310176616478/546948727101718549/unknown.png

 

[Fluff]

As far as I'm aware you cannot use VBA in OpenOffice.

 

[Michael M]

@Fluff
Apparently you can, but only very basic codes.

 

[UrVabsy]

As far as I'm aware you cannot use VBA in OpenOffice.

Can use these options
https://cdn.discordapp.com/attachments/416663310176616478/547328438239887360/unknown.png
I think need to try something different @micheal

 

[gingerbreadgrl]

Hi there,

I am organizing data into a number of different sheets using VBA code, @NateO I am experimenting with using your array code in my macro, it seems to work fine but I need to make a few modifications. My name column is in column B not column A. Also, I only care about extracting the first name, but I would like to put it in column BP (the last column in the worksheet). I would like the heading for that column to say "First Name." Is it possible to modify your code to do that? I'm trying but haven't had any luck yet, I'm new to VBA so this is well beyond what I know. Thank you!!

Dim varArr As Variant
Dim i As Long, strArr() As String
Application.ScreenUpdating = False
With Worksheets(3)
    Let varArr = .Range(.Cells(1, 1), _
        .Cells(.Rows.Count, 1).End(xlUp)).Value
    If IsArray(varArr) Then
        For i = LBound(varArr, 1) To UBound(varArr, 1)
            Let strArr() = Split(varArr(i, 1))
            Let .Cells(i, 2).Resize( _
                , UBound(strArr) + 1).Value = strArr
            Erase strArr
        Next
    Else: .Cells(1, 2).Resize(, 2).Value = Split(varArr)
    End If
End With
Application.ScreenUpdating = True

 

[Peter_SSs]

I am experimenting with using your array code

Unfortunately NateO is no longer with us but see if this helps. Depends a bit just what you data might be like but give this a try in a copy of your workbook.
If not what you want post back with more details and samples of original data and expected results.

Sub First_Name()
  Range("BP1").Value = "First Name"
  Range("B2", Range("B" & Rows.Count).End(xlUp)).TextToColumns Destination:=Range("BP2"), DataType:=xlDelimited, Space:=True, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 9), Array(3, 9))
End Sub

 

[gingerbreadgrl]

Hi Peter_SSs,

Thanks for your response and sorry for the delay! So I am getting a run time error of 1004 with the code. Although I modified it to declare the sheet because there are multiple worksheets in the workbook. Maybe you can't do that with an array? Also, I think its an array but I'm not sure Anyway this is what I did... The first line works and the second line does not.

 Sheets("Cpl-Child Info").Range("BP1").Value = "First Name"
  Sheets("Cpl-Child Info").Range("B2", Range("B" & Rows.Count).End(xlUp)).TextToColumns Destination:=Range("BP2"), DataType:=xlDelimited, Space:=True, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 9), Array(3, 9))

As a side note I tried to step into the code to see if I could get anything more specific for the error. I have the macro tied to a button on the ribbon and when you click the button a message box pops up and asks if you would like to organize the data. If I step into the macro and proceed by pushing F8 the message box comes up and I can't proceed by pushing F8, I have to click yes, at which point the macro runs and then I can't step through it. Is there a way around it so that I can actually step through the macro using F8?

Thanks!
Gingerbreadgrl

 

[MARK858]

Do you get the same error with the code below?

Sheets("Cpl-Child Info").Range("BP1").Value = "First Name"
  Sheets("Cpl-Child Info").Range("B2", Sheets("Cpl-Child Info").Range("B" & Rows.Count).End(xlUp)).TextToColumns Destination:=Range("BP2"), DataType:=xlDelimited, Space:=True, Other:=False, FieldInfo:=Array(Array(1, 1), Array(2, 9), Array(3, 9))