Splitting cells
 

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

Splitting cells
 

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


"Bill" wrote in message
...
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

Splitting cells
 

On May 21, 12:36 pm, Bill wrote:
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
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sincerely,
Leith ROSS

Splitting cells
 

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

Splitting cells
 

On Wed, 21 May 2008 12:36:46 -0700, Bill
wrote:

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

Splitting cells
 

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


--


Gary


"Bill" wrote in message
...
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

Splitting cells
 

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" wrote in message
...
On Wed, 21 May 2008 12:36:46 -0700, Bill
wrote:

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

Splitting cells
 

On Wed, 21 May 2008 23:58:41 -0400, "Rick Rothstein \(MVP - VB\)"
wrote:

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

Splitting cells
 

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" wrote in message
...
On Wed, 21 May 2008 23:58:41 -0400, "Rick Rothstein \(MVP - VB\)"
wrote:

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

Splitting cells
 

On Thu, 22 May 2008 13:00:26 -0400, "Rick Rothstein \(MVP - VB\)"
wrote:

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