Split string query

[vinny2984]

I have ben playing with a bit of code to split up a string and take the part string for another purpose.
The code below, finds the bit of string I want but what I really need to do is then ammend the initial string to make it minus the part string.

Dim myStr As String
myStr = Range("A2").Value
Range("K2").Value = Split(myStr, " ")(0)

The code above does take the first part of the string up to a space and puts it in K2.

I'm now tryimng to find a way to make

mystr=mystr -Split(myStr, " ")(0)   'doesn't work

but i can' find a way to make that work

any ideas would be really helpful
thanks

 

[Jubjab]

Try this:

Sub a()

mystr = Range("A2").Value

y = Right(mystr, Len(x) - Application.WorksheetFunction.Find(" ", mystr))


End Sub

 

[vinny2984]

Re: Split string - Solved

Thanks thats brilliant. below is the variation I've used

myStr = Right(myStr, Len(myStr) - Application.WorksheetFunction.Find(" ", myStr))

many thanks

 

[Jubjab]

Re: Split string - Solved

Yeah, sorry about that x. I changed my test code to use your variable names but seems I missed to change that one.

 

[vinny2984]

here's the final version, it takes the string and splits it up into sections and palces them in seperate cells.
Many thanks

Sub Splititup()
Dim myStr As String
myStr = Range("A2").Value 'mystr= "WM GIBBINS,MATT 0.00 744.00 744.00 0.00"
Range("K2").Value = Split(myStr, " ")(0)
myStr = Right(myStr, Len(myStr) - Application.WorksheetFunction.Find(" ", myStr))
Range("K3").Value = Split(myStr, ",")(0)
myStr = Right(myStr, Len(myStr) - Application.WorksheetFunction.Find(",", myStr))
Range("K4").Value = Split(myStr, " ")(0)
myStr = Right(myStr, Len(myStr) - Application.WorksheetFunction.Find(" ", myStr))
Range("K5").Value = Split(myStr, " ")(0)
myStr = Right(myStr, Len(myStr) - Application.WorksheetFunction.Find(" ", myStr))
Range("K6").Value = Split(myStr, " ")(0)
myStr = Right(myStr, Len(myStr) - Application.WorksheetFunction.Find(" ", myStr))
Range("K7").Value = Split(myStr, " ")(0)
myStr = Right(myStr, Len(myStr) - Application.WorksheetFunction.Find(" ", myStr))

End Sub

 

[Weaver]

I know I came late to this party, but here's another potential solution:

mystr = Trim(Replace(mystr, Split(mystr, " ")(0)&" ", ""))

 

[Weaver]

This might be a slightly better way of weeding out the double spaces potentially remaining if you start nipping bits out the middle of the string, rather than the start

mystr = Trim(Replace(Replace(mystr, Split(mystr, " ")(0), ""), "  ", " "))

 

[vinny2984]

Thanks for both of those alternatives, i'll play around with all the solutions and see which suits the purpose bst
many thanks

 

[Peter_SSs]

Instead of that great long Application.Worksheetfunction.Find you can use vba's InStr() function.

Try something like this:

myStr = Right(myStr, Len(myStr) - InStr(myStr, " "))

 

[pgc01]

Hi

Another option for your sub:

Sub Splititup()
Dim arrStr As Variant
 
arrStr = Split(Replace(Range("A2").Value, ",", " "))
 
Range("K2").Resize(1 + UBound(arrStr)) = Application.Transpose(arrStr)
 
End Sub