Splitting cells

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bill - 21 May 2008 20:36 GMT

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

Reply to this Message

Paul W Smith - 21 May 2008 21:02 GMT

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

>I have data like the following.
>
[quoted text clipped - 8 lines]
> Thanks
> Bill

Reply to this Message

Leith Ross - 21 May 2008 21:03 GMT

> I have data like the following.
>
[quoted text clipped - 7 lines]
> 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

Reply to this Message

merjet - 21 May 2008 21:05 GMT

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

Reply to this Message

Ron Rosenfeld - 21 May 2008 21:30 GMT

>I have data like the following.
>
[quoted text clipped - 7 lines]
>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

Reply to this Message

Rick Rothstein (MVP - VB) - 22 May 2008 04:58 GMT

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

>>I have data like the following.
>>
[quoted text clipped - 21 lines]
> ==========================================
> --ron

Reply to this Message

Ron Rosenfeld - 22 May 2008 12:25 GMT

On Wed, 21 May 2008 23:58:41 -0400, "Rick Rothstein \(MVP - VB\)"
<rick.newsNO.SPAM@NO.SPAMverizon.net> 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

Reply to this Message

Rick Rothstein (MVP - VB) - 22 May 2008 18:00 GMT

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

> On Wed, 21 May 2008 23:58:41 -0400, "Rick Rothstein \(MVP - VB\)"
> <rick.newsNO.SPAM@NO.SPAMverizon.net> wrote:
[quoted text clipped - 8 lines]
> Very cute!
> --ron

Reply to this Message

Ron Rosenfeld - 22 May 2008 21:22 GMT

On Thu, 22 May 2008 13:00:26 -0400, "Rick Rothstein \(MVP - VB\)"
<rick.newsNO.SPAM@NO.SPAMverizon.net> 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

Reply to this Message

Gary Keramidas - 21 May 2008 21:41 GMT

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

Signature

Gary

>I have data like the following.
>
[quoted text clipped - 7 lines]
> Thanks
> Bill

Reply to this Message