Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
STRIP CHARACTERS
I looking for awat to break up a cell into 3 different cells.
I can use 3 different formulas if I have too. I get data like below 123X324X3333 or 1234x45x4343 The amount of numbers may vary abd the X may be x or X. I can get te first set of numbers with =LEFT(Q10,MATCH(FALSE,ISNUMBER(-MID(Q10,ROW(INDIRECT("1:8")), 1)),FALSE)-1) But cannot get the middle or end set of numbers, Any Ideas? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
STRIP CHARACTERS
In xl2003, I would do:
Select the range and change that delimiter to a common character (x is different from X). Edit|replace what: x with: | replace all Then select one column at a time and use: Data|Text to columns Delimited by Other (|) and finish up the wizard. rpick60 wrote: I looking for awat to break up a cell into 3 different cells. I can use 3 different formulas if I have too. I get data like below 123X324X3333 or 1234x45x4343 The amount of numbers may vary abd the X may be x or X. I can get te first set of numbers with =LEFT(Q10,MATCH(FALSE,ISNUMBER(-MID(Q10,ROW(INDIRECT("1:8")), 1)),FALSE)-1) But cannot get the middle or end set of numbers, Any Ideas? -- Dave Peterson |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
STRIP CHARACTERS
Hi,
This works but there may be (surely is) something simpler. With your string in A1 put these formula in B1, C1 and D1 respictively. =LEFT(A1,SEARCH("x",A1,1)-1) =MID(A1,SEARCH("x",A1,LEN(B1))+1,(SEARCH("x",A1,SE ARCH("x",A1,LEN(B1))+1)-1)-SEARCH("x",A1,1)) =RIGHT(A1,LEN(A1)-((SEARCH("x",A1,SEARCH("x",A1,LEN(B1))+1)-1)+1)) Mike "rpick60" wrote: I looking for awat to break up a cell into 3 different cells. I can use 3 different formulas if I have too. I get data like below 123X324X3333 or 1234x45x4343 The amount of numbers may vary abd the X may be x or X. I can get te first set of numbers with =LEFT(Q10,MATCH(FALSE,ISNUMBER(-MID(Q10,ROW(INDIRECT("1:8")), 1)),FALSE)-1) But cannot get the middle or end set of numbers, Any Ideas? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
STRIP CHARACTERS
Option Compare Text 'at TOP of module
Sub findxinstring() For Each c In Range("e2:e3") p1 = InStr(c, "x") 'MsgBox p1 p2 = p1 + InStr(p1, c, "x") - 1 'MsgBox p2 c.Offset(, 1) = Left(c, p1 - 1) c.Offset(, 2) = Mid(c, p1 + 1, p2 - p1) c.Offset(, 3) = Right(c, Len(c) - 1 - p2) Next c End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "rpick60" wrote in message ... I looking for awat to break up a cell into 3 different cells. I can use 3 different formulas if I have too. I get data like below 123X324X3333 or 1234x45x4343 The amount of numbers may vary abd the X may be x or X. I can get te first set of numbers with =LEFT(Q10,MATCH(FALSE,ISNUMBER(-MID(Q10,ROW(INDIRECT("1:8")), 1)),FALSE)-1) But cannot get the middle or end set of numbers, Any Ideas? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
STRIP CHARACTERS
Thanks to all they worked great!
On Dec 15, 2:36 pm, "Don Guillett" wrote: Option Compare Text 'at TOP of module Sub findxinstring() For Each c In Range("e2:e3") p1 = InStr(c, "x") 'MsgBox p1 p2 = p1 + InStr(p1, c, "x") - 1 'MsgBox p2 c.Offset(, 1) = Left(c, p1 - 1) c.Offset(, 2) = Mid(c, p1 + 1, p2 - p1) c.Offset(, 3) = Right(c, Len(c) - 1 - p2) Next c End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "rpick60" wrote in message ... I looking for awat to break up a cell into 3 different cells. I can use 3 different formulas if I have too. I get data like below 123X324X3333 or 1234x45x4343 The amount of numbers may vary abd the X may be x or X. I can get te first set of numbers with =LEFT(Q10,MATCH(FALSE,ISNUMBER(-MID(Q10,ROW(INDIRECT("1:8")), 1)),FALSE)-1) But cannot get the middle or end set of numbers, Any Ideas?- Hide quoted text - - Show quoted text - |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
STRIP CHARACTERS
On Sat, 15 Dec 2007 10:27:08 -0800 (PST), rpick60 wrote:
I looking for awat to break up a cell into 3 different cells. I can use 3 different formulas if I have too. I get data like below 123X324X3333 or 1234x45x4343 The amount of numbers may vary abd the X may be x or X. I can get te first set of numbers with =LEFT(Q10,MATCH(FALSE,ISNUMBER(-MID(Q10,ROW(INDIRECT("1:8")), 1)),FALSE)-1) But cannot get the middle or end set of numbers, Any Ideas? Here's another VBA approach: ============================== Option Explicit Sub splitatX() Dim c As Range Dim temp Dim i As Long For Each c In Selection temp = Split(LCase(c.Text), "x") For i = 0 To UBound(temp) c.Offset(0, i + 1).Value = temp(i) Next i Next c End Sub =================================== --ron |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
STRIP CHARACTERS
Here's another VBA approach:
============================== Option Explicit Sub splitatX() Dim c As Range Dim temp Dim i As Long For Each c In Selection temp = Split(LCase(c.Text), "x") For i = 0 To UBound(temp) c.Offset(0, i + 1).Value = temp(i) Next i Next c End Sub =================================== You can eliminate the LCase function call for the first statement in your For-Next block by using the Split function's "Compare" argument... temp = Split(c.Text, "x", , vbTextCompare) Rick |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
STRIP CHARACTERS
On Sat, 15 Dec 2007 23:34:36 -0500, "Rick Rothstein \(MVP - VB\)"
wrote: Here's another VBA approach: ============================== Option Explicit Sub splitatX() Dim c As Range Dim temp Dim i As Long For Each c In Selection temp = Split(LCase(c.Text), "x") For i = 0 To UBound(temp) c.Offset(0, i + 1).Value = temp(i) Next i Next c End Sub =================================== You can eliminate the LCase function call for the first statement in your For-Next block by using the Split function's "Compare" argument... temp = Split(c.Text, "x", , vbTextCompare) Rick Thanks for that pointer. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Strip text before character | Excel Worksheet Functions | |||
Strip Out Time | New Users to Excel | |||
Strip carriage return, add row | Excel Worksheet Functions | |||
TAB STRIP | Excel Discussion (Misc queries) | |||
tab strip | New Users to Excel |