I want to find out the what the formula would be to perform a function
similar to what the "Text to Column" currently does.

I have a set of data:
CCS:OPS:S/M
GA:HR:G/A
SALES:EURO:NEO

I want to write TWO functions that would "cut" the data at the colon.

FIRST function would return just the first part of the dataset, resulting in:
CCS
GA
SALES

SECOND function would return the first and second parts of the dataset,
resulting in:
CCS:OPS
GA:HR
SALES:EURO

Any thoughts?
--
Thank you! - Jennifer

To clarify, I'm looking for what I should enter in the macro :)
--
Thank you! - Jennifer


"Jennifer Cali" wrote:

I want to find out the what the formula would be to perform a function
similar to what the "Text to Column" currently does.

I have a set of data:
CCS:OPS:S/M
GA:HR:G/A
SALES:EURO:NEO

I want to write TWO functions that would "cut" the data at the colon.

FIRST function would return just the first part of the dataset, resulting in:
CCS
GA
SALES

SECOND function would return the first and second parts of the dataset,
resulting in:
CCS:OPS
GA:HR
SALES:EURO

Any thoughts?
--
Thank you! - Jennifer

In xl2k and above, you could use split.

Option Explicit
Sub testme01()

Dim myStr As String
Dim mySplit As Variant
Dim iCtr As Long
Dim HowManyPieces As Long

myStr = "CCS:OPS:S/M"

If InStr(1, myStr, ":", vbTextCompare) 0 Then
mySplit = Split(myStr, ":")
HowManyPieces = UBound(mySplit) - LBound(mySplit) + 1
MsgBox mySplit(LBound(mySplit))
If HowManyPieces 1 Then
MsgBox mySplit(LBound(mySplit)) & ":" & mySplit(LBound(mySplit) + 1)
Else
MsgBox "Only one piece"
End If
Else
MsgBox "No Colons"
End If

End Sub

xl2k+ also has an "opposite" function called Join to join strings together.

If you don't have xl2k+, you could use instr to look for positions of the colons
and split it yourself.

Jennifer Cali wrote:

To clarify, I'm looking for what I should enter in the macro :)
--
Thank you! - Jennifer

"Jennifer Cali" wrote:

I want to find out the what the formula would be to perform a function
similar to what the "Text to Column" currently does.

I have a set of data:
CCS:OPS:S/M
GA:HR:G/A
SALES:EURO:NEO

I want to write TWO functions that would "cut" the data at the colon.

FIRST function would return just the first part of the dataset, resulting in:
CCS
GA
SALES

SECOND function would return the first and second parts of the dataset,
resulting in:
CCS:OPS
GA:HR
SALES:EURO

Any thoughts?
--
Thank you! - Jennifer

--

Dave Peterson

Thanks, Dave. I think this will work!!!
--
Thank you! - Jennifer


"Dave Peterson" wrote:

In xl2k and above, you could use split.

Option Explicit
Sub testme01()

Dim myStr As String
Dim mySplit As Variant
Dim iCtr As Long
Dim HowManyPieces As Long

myStr = "CCS:OPS:S/M"

If InStr(1, myStr, ":", vbTextCompare) 0 Then
mySplit = Split(myStr, ":")
HowManyPieces = UBound(mySplit) - LBound(mySplit) + 1
MsgBox mySplit(LBound(mySplit))
If HowManyPieces 1 Then
MsgBox mySplit(LBound(mySplit)) & ":" & mySplit(LBound(mySplit) + 1)
Else
MsgBox "Only one piece"
End If
Else
MsgBox "No Colons"
End If

End Sub

xl2k+ also has an "opposite" function called Join to join strings together.

If you don't have xl2k+, you could use instr to look for positions of the colons
and split it yourself.

Jennifer Cali wrote:

To clarify, I'm looking for what I should enter in the macro :)
--
Thank you! - Jennifer

"Jennifer Cali" wrote:

I want to find out the what the formula would be to perform a function
similar to what the "Text to Column" currently does.

I have a set of data:
CCS:OPS:S/M
GA:HR:G/A
SALES:EURO:NEO

I want to write TWO functions that would "cut" the data at the colon.

FIRST function would return just the first part of the dataset, resulting in:
CCS
GA
SALES

SECOND function would return the first and second parts of the dataset,
resulting in:
CCS:OPS
GA:HR
SALES:EURO

Any thoughts?
--
Thank you! - Jennifer

--

Dave Peterson

If you're looking for a macro, here's one that will take the value in
A1 and put it in B1 to however many colons you have, on the active
sheet. Assumes Option Base 0.

Public Sub TextToCol()
Dim rng1 As Excel.Range
Dim rng2 As Excel.Range
Dim v As Variant

Set rng1 = ActiveSheet.Range("A1")
Set rng2 = ActiveSheet.Range("B1")

v = Split(rng1, ":")

If UBound(v) = 0 Then
rng2.Value = v(0)
Else
rng2.Resize(1, UBound(v) + 1).Value = v
End If
End Sub

On Jan 8, 5:22 pm, Jennifer Cali
wrote:
To clarify, I'm looking for what I should enter in the macro :)
--
Thank you! - Jennifer

"Jennifer Cali" wrote:
I want to find out the what the formula would be to perform a function
similar to what the "Text to Column" currently does.

I have a set of data:
CCS:OPS:S/M
GA:HR:G/A
SALES:EURO:NEO

I want to write TWO functions that would "cut" the data at the colon.

FIRST function would return just the first part of the dataset, resulting in:
CCS
GA
SALES

SECOND function would return the first and second parts of the dataset,
resulting in:
CCS:OPS
GA:HR
SALES:EURO

Any thoughts?
--
Thank you! - Jennifer

On Tue, 8 Jan 2008 14:08:01 -0800, Jennifer Cali
wrote:

I want to find out the what the formula would be to perform a function
similar to what the "Text to Column" currently does.

I have a set of data:
CCS:OPS:S/M
GA:HR:G/A
SALES:EURO:NEO

I want to write TWO functions that would "cut" the data at the colon.

FIRST function would return just the first part of the dataset, resulting in:
CCS
GA
SALES

SECOND function would return the first and second parts of the dataset,
resulting in:
CCS:OPS
GA:HR
SALES:EURO

Any thoughts?


=======================
Option Explicit
Function ParseColon(str As String, Optional Part1 As Boolean = True)
'If Part1 = True, or is not specified, return first part only
'If Part1 = False, return first and second parts
Dim sTemp() As String

sTemp = Split(str, ":")
ParseColon = sTemp(0)
If Part1 = False Then
On Error Resume Next
ParseColon = ParseColon & ":" & sTemp(1)
End If
End Function
=============================
--ron

I want to find out the what the formula would be to perform a function
similar to what the "Text to Column" currently does.

I have a set of data:
CCS:OPS:S/M
GA:HR:G/A
SALES:EURO:NEO

I want to write TWO functions that would "cut" the data at the colon.

FIRST function would return just the first part of the dataset, resulting in:
CCS
GA
SALES

SECOND function would return the first and second parts of the dataset,
resulting in:
CCS:OPS
GA:HR
SALES:EURO

=======================
Option Explicit
Function ParseColon(str As String, Optional Part1 As Boolean = True)
'If Part1 = True, or is not specified, return first part only
'If Part1 = False, return first and second parts
Dim sTemp() As String

sTemp = Split(str, ":")
ParseColon = sTemp(0)
If Part1 = False Then
On Error Resume Next
ParseColon = ParseColon & ":" & sTemp(1)
End If
End Function
=============================

Let me make it clear to those reading this tread... use Ron's function (and not what I'm about to post); however, one-liners are a "thing" with me (I sort of have a reputation about them over in the compiled VB newsgroups) and I wanted to see if I could come up with one for this particular function. For those who find this kind of thing as interesting as I do, here is the result...

Function ParseColon(str As String, Optional Part1 As Boolean = True) As String
ParseColon = Replace(Split(Replace(str, ":", "|", , 1 + Part1), ":")(0), "|", ":")
End Function

It should now be clear why I said to use Ron's function... readability (and probably efficiency too).

Two observations, though... I would add "As String" to Ron's function declaration and I don't think the "On Error Resume Next" statement is needed.

Rick

Two observations, though... I would add "As String"
to Ron's function declaration and I don't think the
"On Error Resume Next" statement is needed.

I see why you included the "On Error Resume Next"... to handle the case when
the text passed into the function is the Empty String. You can still remove
it by adding a couple of UBound tests to your code instead...

Function ParseColon(Str As String, Optional Part1 As Boolean = True) As
String
'If Part1 = True, or is not specified, return first part only
'If Part1 = False, return first and second parts
Dim sTemp() As String
sTemp = Split(Str, ":")
If UBound(sTemp) = 0 Then
ParseColon = sTemp(0)
If Part1 = False And UBound(sTemp) 0 Then
ParseColon = ParseColon & ":" & sTemp(1)
End If
End If
End Function

And, of course, my one-liner needs to handle the Empty String argument as
well. Here is how I retooled my function to keep it a one-liner...

Function ParseColon(Str As String, Optional Part1 As Boolean = True) As
String
ParseColon = Replace(Split(Replace(Str & String(-(Str = ""), ":"), ":",
"|", , 1 + Part1), ":")(0), "|", Left(":", -(Str < "")))
End Function

Rick

On Tue, 8 Jan 2008 20:50:50 -0500, "Rick Rothstein \(MVP - VB\)"
wrote:

Two observations, though... I would add "As String" to Ron's function declaration and I don't think the "On Error Resume Next" statement is needed.

Those are both the way they are purposely, as I don't know what the OP wants to
do with conditions when there might be none or only one component to the
string.

With the "return" as a variant, then it can be set as an error value.

With it set as a string, it will return a blank.

The On Error Resume Next avoids having to do testing to see what's there.
Moving it earlier, and also setting the return to string, would return a blank
if there is no data, but the OP has not specified what he wants.

For example:

=================================
Option Explicit
Function ParseColon(str As String, Optional Part1 As Boolean = True) As String
'If Part1 = True, or is not specified, return first part only
'If Part1 = False, return first and second parts
Dim sTemp() As String
On Error Resume Next
sTemp = Split(str, ":")
ParseColon = sTemp(0)
If Part1 = False Then
ParseColon = ParseColon & ":" & sTemp(1)
End If
End Function
======================================

will return a blank if there is no data in the original.

If you'll allow for two lines, one could accomplish the same with :

=============================================
Function PC2(str As String, Optional Part1 As Boolean = True) As String
'If Part1 = True, or is not specified, return first part only
'If Part1 = False, return first and second parts
On Error Resume Next
PC2 = Split(str, ":")(0) & IIf(Part1, "", ":" & Split(str, ":")(1))
End Function
=============================

Without the On Error ... statement, the above would be a one-liner, but would
give a #VALUE error if the subject is blank.

Your one-liners are "neat", but I think overly complex in this instance:

ParseColon = Replace(Split(Replace(Str & String(-(Str = ""), ":"), ":",
"|", , 1 + Part1), ":")(0), "|", Left(":", -(Str < "")))


Of course, you could always use my "hammer":

=====================================
Function PC4(str As String, Optional Part1 As Boolean = True) As String
Dim re As Object, mc As Object
Set re = CreateObject("vbscript.regexp")
re.Pattern = IIf(Part1, "(\w+)(:\w+.*)?", "(\w+(:\w+)?).*")
PC4 = re.Replace(str, "$1")
End Function
=======================================
--ron

I want to write TWO functions that would "cut" the data at the colon.

If you would like to return the values in two adjacent columns, here's a
different idea.
Suppose you have data in A1. Select B1:C1, and array enter the following:

=FirstTwo(A1)

Function FirstTwo(s)
Dim p1 As Long
Dim p2 As Long
p1 = InStr(1, s, ":")
p2 = InStr(p1 + 1, s, ":")
FirstTwo = Array(Left$(s, p1 - 1), Left$(s, p2 - 1))
End Function


--
HTH
Dana DeLouis



"Jennifer Cali" wrote in message
...
I want to find out the what the formula would be to perform a function
similar to what the "Text to Column" currently does.

I have a set of data:
CCS:OPS:S/M
GA:HR:G/A
SALES:EURO:NEO

I want to write TWO functions that would "cut" the data at the colon.

FIRST function would return just the first part of the dataset, resulting
in:
CCS
GA
SALES

SECOND function would return the first and second parts of the dataset,
resulting in:
CCS:OPS
GA:HR
SALES:EURO

Any thoughts?
--
Thank you! - Jennifer

Excel 2007 only - here is a combination of worksheet functions, array-
entered in as many columns as you have colons, that will do this
without VBA. Data is in A1.

In B1, formula is:

B1 =LEN(A1)-LEN(SUBSTITUTE(A1,":",""))

In C1:J1, formula is array-entered as follows:

=TRANSPOSE(MID(A1,IFERROR(FIND("^",SUBSTITUTE(A1," :","^",ROW(INDIRECT("1:"&B1+1))-1))
+1,1),IFERROR(FIND("^",SUBSTITUTE(A1,":","^",ROW(I NDIRECT("1:"&B1+1)))),
1+LEN(A1))-
IFERROR(FIND("^",SUBSTITUTE(A1,":","^",ROW(INDIREC T("1:"&B1+1))-1))
+1,1)))

However, it will return #N/A for any cells that will not have values.
In other words, in this example:

A1 ="SALES:EURO:NEO:BUDRO:LALA"

Values in C1:J1 as as follows:

SALES EURO NEO BUDRO LALA #N/A #N/A #N/A

I'm guessing the VBA approach is more efficient in this case.


On Jan 8, 5:08 pm, Jennifer Cali
wrote:
I want to find out the what the formula would be to perform a function
similar to what the "Text to Column" currently does.

I have a set of data:
CCS:OPS:S/M
GA:HR:G/A
SALES:EURO:NEO

I want to write TWO functions that would "cut" the data at the colon.

FIRST function would return just the first part of the dataset, resulting in:
CCS
GA
SALES

SECOND function would return the first and second parts of the dataset,
resulting in:
CCS:OPS
GA:HR
SALES:EURO

Any thoughts?
--
Thank you! - Jennifer