Splitting cells

[Bill]

I have data like the following.

6.00 x 12.00 x 24.00 CRS

I would like to split out the last part (CRS) into another cell. There is
always a space before the data to split, but the rest may or may not have
spaces. Basically I need to go to the end of the cell and then back to the
space and split.

Thanks
Bill

 

[Paul W Smith]

Try a user defined functuion like this:

Function Example(Text As String) As String
Dim Position As Integer
Position = Len(Text)
Do
Position = Position - 1
Debug.Print Mid(Text, Position, 1)
Loop Until Mid(Text, Position, 1) = " "
Example = Mid(Text, Position + 1)
End Function

 

[Leith Ross]

I have data like the following.

6.00 x 12.00 x 24.00 CRS

I would like to split out the last part (CRS) into another cell. There is
always a space before the data to split, but the rest may or may not have
spaces. Basically I need to go to the end of the cell and then back to the
space and split.

Thanks
Bill

Hello Bill,

Here is a macro that will remove the last part of the string. This
will work with Excel 2000 and up.

Macro Code
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
\
Sub Macro1()

Dim MyStr As String
Dim S As Long

MyStr = "6.00 x 12.00 x 24.00 CRS"
S = InStrRev(MyStr, " ")
MyStr = Left(MyStr, S)

End Sub

 

[merjet]

Sub Split()
Dim rng As Range
Dim c As Range
Set rng = Application.InputBox("Select range.", Type:=8)
For Each c In rng
c.Offset(0, 1) = Right(c, 3)
c = Trim(c) 'may not need
c = Left(c, Len(c) - 4)
Next c
End Sub

 

[Ron Rosenfeld]

I have data like the following.

6.00 x 12.00 x 24.00 CRS

I would like to split out the last part (CRS) into another cell. There is
always a space before the data to split, but the rest may or may not have
spaces. Basically I need to go to the end of the cell and then back to the
space and split.

Thanks
Bill

Here are two ways to return the last "word"

==================================================
Function lastword1(str As String) As String
lastword1 = Mid(str, InStrRev(str, " ") + 1)
End Function

Function lastword2(str As String) As String
lastword2 = Split(str)(UBound(Split(str)))
End Function
==========================================
--ron

 

[Gary Keramidas]

i use something like this:
Sub test()
Dim lastword As Variant
lastword = Split(Range("A1"), " ")
Debug.Print lastword(UBound(lastword))
End Sub

 

[Rick Rothstein \(MVP - VB\)]

Here's one more to add to your collection, although I would guess most
people will find it surprising that it works.<g>

Function LastWord2(Str As String) As String
LastWord2 = Replace(Str, " ", "", InStrRev(Str, " "))
End Function


Rick

 

[Ron Rosenfeld]

Here's one more to add to your collection, although I would guess most
people will find it surprising that it works.<g>

Function LastWord2(Str As String) As String
LastWord2 = Replace(Str, " ", "", InStrRev(Str, " "))
End Function

Very cute!
--ron

 

[Rick Rothstein \(MVP - VB\)]

Cute, yes... plus it is some 2 to 3 times faster than the Split function
method you posted, although it is some 4 to 5 times slower than the Mid
function... the Mid function method is the quickest solution.

Rick

 

[Ron Rosenfeld]

Cute, yes... plus it is some 2 to 3 times faster than the Split function
method you posted, although it is some 4 to 5 times slower than the Mid
function... the Mid function method is the quickest solution.

Rick

So I guess I posted both the fastest and the slowest solutions )

But, wrt to your Replace method, I particularly liked how you didn't need to
add 1 to skip over the <space>.
--ron