Function instead of "Text to Column"

J

Jennifer Cali

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?
 
D

Dave Peterson

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.
 
R

Ron Rosenfeld

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?
Click to expand...


=======================
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
 
R

Rick Rothstein \(MVP - VB\)

I want to find out the what the formula would be to perform a function
=======================
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
=============================
Click to expand...

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
 
D

Dana DeLouis

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
 
R

Rick Rothstein \(MVP - VB\)

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.
Click to expand...

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
 
R

Ron Rosenfeld

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.
Click to expand...

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

ilia

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
 
I

ilia

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(INDIRECT("1:"&B1+1)))),
1+LEN(A1))-
IFERROR(FIND("^",SUBSTITUTE(A1,":","^",ROW(INDIRECT("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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Code to do a simple clean of one column by copying and pasting(finding and replacing) phrases in adj 4
problem returning a range from a function 7
Selection of range for the 'slope' function 1
How can I seperate Imported text with delimeter as part of the par 5
Wrong data type returned from user-defined function 9
Help with text file 3
COUNTIF function help please 8
Function to return column where a date is entered 1

Top