Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
splitting cells | New Users to Excel | |||
Splitting cells | Excel Worksheet Functions | |||
Splitting cells | Excel Discussion (Misc queries) | |||
Splitting cells? | Excel Discussion (Misc queries) | |||
splitting cells | Excel Worksheet Functions |