Need to split the text in a cell

[Marky_B]

Hi I currently have this code

Sub PostToRegister()
'Copies Invoice data to the Invoice Register
Dim WS1 As Worksheet
Dim WS2 As Worksheet

Set WS1 = Worksheets("Invoice")
Set WS2 = Worksheets("Summary")

'Figures out which row is the next row
nextrow = WS2.Cells(Rows.Count, 2).End(xlUp).Row + 1

'Write the Information to the Register
WS2.Cells(nextrow, 2).Resize(1, 6).Value = Array(WS1.Range("B9"), WS1.Range("E8"), WS1.Range("E9"), Range("InvBC"), Range("InvASC"), Range("InvTotal"))

End Sub

I now have the need to split the text in Cell "B9" when its extracted and paste it as 2 separate cells. It contains a forename and a surname like this "Jo Bloggs" or this "Jo-jo Bloggs" the delimitator is a " "(space)

thank you

 

[smitpau]

Can you record a macro of you using the Text to Columns tool and try to replicate that as the next line in the Macro.

 

[Marky_B]

the data is dynamic so that wouldn't work. I though about doing that though.
Could you please help on the code which takes the cell text and splits it into 2?
thank you

 

[smitpau]

Can you make the original dynamic table do the text splitting using info from the link then reference B9 and C9 in the macro.

Split text into different columns with functions - Microsoft Support

You can use the LEFT, MID, RIGHT, SEARCH, and LEN text functions to manipulate strings of text in your data.

support.office.com

 

[Joe4]

Try this:

    Dim fname As String, lname As String
    fname = Left(WS2.Range("B9"), InStr(WS2.Range("B9"), " ") - 1)
    lname = Mid(WS2.Range("B9"), InStr(WS2.Range("B9"), " ") + 1)

and then you can put fname and lname values in any cells you want.

 

[Marky_B]

thank you, where about's would i insert this code?

 

[Joe4]

thank you, where about's would i insert this code?

At the bottom of your code, presumably.
Did you not write that code yourself?

 

[Marky_B]

yes however, your code doesn't work within mine??
sorry.

 

[Marky_B]

i know this is the line that i need to change but dont know how

WS2.Cells(nextrow, 2).Resize(1, 6).Value = Array(WS1.Range("B9"), WS1.Range("E8"), WS1.Range("E9"), Range("InvBC"), Range("InvASC"), Range("InvTotal"))
WS1.Range("B9") this is the location the forename and surname is located, just don't know how to pull and split the text into 2 cells

thank you

 

[Joe4]

What is that line currently doing?
What range is it writing to, and what exactly is it writing to that range?