PDA

View Full Version : [SOLVED] Find last " " (space) in String



Cass
01-12-2006, 05:51 AM
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
01-12-2006, 06:30 AM
Sub Separate()
'Split text
Range("A3") = Split(Range("A1"), " ")(5)
'Count spaces
Range("A4") = UBound(Split(Range("A1"), " "))
End Sub

Bob Phillips
01-12-2006, 07:47 AM
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
01-12-2006, 12:53 PM
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
01-16-2006, 05:00 AM
=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 :think:

Bob Phillips
01-16-2006, 06:16 AM
And now all this into VBA :think:

Why?

Cass
01-16-2006, 06:33 AM
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 :yes
(Using your formula and then replace the result with "")

final result must be F PLUS S30

Bob Phillips
01-16-2006, 06:43 AM
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 :yes
(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
01-16-2006, 12:23 PM
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