# Split string query

#### vinny2984

##### Board Regular
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.

HTML:
``````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
Code:
``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

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

#### Jubjab

##### Well-known Member
Try this:

Code:
``````Sub a()

mystr = Range("A2").Value

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

End Sub``````

#### vinny2984

##### Board Regular
Re: Split string - Solved

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

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

many thanks

#### Jubjab

##### Well-known Member
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

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

Code:
``````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

##### Well-known Member
I know I came late to this party, but here's another potential solution:

Code:
``mystr = Trim(Replace(mystr, Split(mystr, " ")(0)&" ", ""))``

#### Weaver

##### Well-known Member
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

Code:
``mystr = Trim(Replace(Replace(mystr, Split(mystr, " ")(0), ""), "  ", " "))``

#### vinny2984

##### Board Regular
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

##### MrExcel MVP, Moderator
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

##### MrExcel MVP
Hi

Code:
``````Sub Splititup()
Dim arrStr As Variant

arrStr = Split(Replace(Range("A2").Value, ",", " "))

Range("K2").Resize(1 + UBound(arrStr)) = Application.Transpose(arrStr)

End Sub``````

Replies
4
Views
315
Replies
1
Views
429
Replies
9
Views
503
Replies
2
Views
391
Replies
4
Views
465

1,191,717
Messages
5,988,262
Members
440,146
Latest member
rgomes8

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back