Find last " " (space) in String

[Cass]

Help!

Separate string CO SM F PLUS S30 JYSN 08002000
if i use =mid(str1;7;(len(str1)-16) then result F PLUS S30 JYSN
Constant number is first 6 and last 9 character
Now how separate the last string part (blue) whose length is variable.

second question count spaces in string?! (herein example is 6 space) [FindNext method]?!

[mdmackillop]

 Sub Separate()
'Split text
Range("A3") = Split(Range("A1"), " ")(5)
'Count spaces
Range("A4") = UBound(Split(Range("A1"), " "))
End Sub

[Bob Phillips]

Help!

Separate string CO SM F PLUS S30 JYSN 08002000
if i use =mid(str1;7;(len(str1)-16) then result F PLUS S30 JYSN
Constant number is first 6 and last 9 character
Now how separate the last string part (blue) whose length is variable.

second question count spaces in string?! (herein example is 6 space) [FindNext method]?!

Assuming your data is in A21, and that first formula is in B21, then in C21 use

=MID(B21,FIND("~",SUBSTITUTE(B21," ","~",LEN(B21)-LEN(SUBSTITUTE(B21," ",""))))+1,99)

or directly upon A21

=MID(A21,FIND("~",SUBSTITUTE(A21," ","~",LEN(A21)-LEN(SUBSTITUTE(A21," ",""))-1))+1,
FIND("~",SUBSTITUTE(A21," ","~",LEN(A21)-LEN(SUBSTITUTE(A21," ",""))))-
FIND("~",SUBSTITUTE(A21," ","~",LEN(A21)-LEN(SUBSTITUTE(A21," ",""))-1)))

[mdmackillop]

To simplify formula entry, you could use a user defined function (UDF). Put the following code in a standard module, and enter =Part(A21,5) as your formula.

 
Function Part(Data As Range, i As Long)
Part = Split(Data, " ")(i)
End Function

To count Spaces, or (letters) in a cell =LCount(A21," ") or =LCount(A21,"e")

 
Function LCount(Data As Range, i As String)
LCount = UBound(Split(Data, i))
End Function

[Cass]

=MID(A21,FIND("~",SUBSTITUTE(A21," ","~",LEN(A21)-LEN(SUBSTITUTE(A21," ",""))-1))+1,
FIND("~",SUBSTITUTE(A21," ","~",LEN(A21)-LEN(SUBSTITUTE(A21," ",""))))-
FIND("~",SUBSTITUTE(A21," ","~",LEN(A21)-LEN(SUBSTITUTE(A21," ",""))-1)))

And now all this into VBA

[Bob Phillips]

And now all this into VBA

Why?

[Cass]

because there are more space to play different cases.
All the source data isn't the same rules written, sometimes is the last place (etc. JYSN) necessary
(etc. if source data contains less than or equal 3 spaces, then don't need remove the last but one word and so on )
but anyway i don't need the last but one word

I need remove this last word
(Using your formula and then replace the result with "")

final result must be F PLUS S30

[Bob Phillips]

because there are more space to play different cases.
All the source data isn't the same rules written, sometimes is the last place (etc. JYSN) necessary
(etc. if source data contains less than or equal 3 spaces, then don't need remove the last but one word and so on )
but anyway i don't need the last but one word

I need remove this last word
(Using your formula and then replace the result with "")

final result must be F PLUS S30

What exactly are you trying to achieve, you are changing from what you originally stated?

If you want to remove the last word, then use

=LEFT(A1,FIND("~",SUBSTITUTE(A1," ","~",LEN(A1)-LEN(SUBSTITUTE(A1," ","")))))

If you want anything before JSNY, then use

=LEFT(A1,FIND("JYSN",A1)-1)

VBA should be the very last resort, not the first.

[mdmackillop]

I thought you were looking for the JYSN string.

If you need the 3 previous strings "F Plus S30" then the following should suffice.

 
Function Part(Data As Range)
For i = 2 To 4
   Part = Part & Split(Data, " ")(i) & " "
Next
End Function