STRIP CHARACTERS

[rpick60]

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]

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.

 

[Mike H]

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,SEARCH("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

 

[Don Guillett]

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

 

[rpick60]

Thanks to all they worked great!

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






- Show quoted text -

 

[Ron Rosenfeld]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Ron Rosenfeld]

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