UDF to return text
 

Hi all, I'm trying to figure out how to make a UDF obtain a string
from any given cell, split and return the value depending on what the
user asks for.

For example.

Cell value in A1 = 233-2330100-100102-101-99999999-999999
There is 6 segments to this string....

The idea was that I would enter in cell A2

=CoA(A1,2)

2 representing the second segement.

The UDF would be something like

Public Function CoA(account as string, xyz) as number
Select Case xyz
Case 1
CoA = left(account, 3)
Case 2
CoA = Mid(account, 7, 3)
Case n
CoA = n
End Select
End Function

I'm just struggling with what I need to include for it to work.

UDF to return text
 

Forgone wrote:
Hi all, I'm trying to figure out how to make a UDF obtain a string
from any given cell, split and return the value depending on what the
user asks for.

For example.

Cell value in A1 = 233-2330100-100102-101-99999999-999999
There is 6 segments to this string....

The idea was that I would enter in cell A2

=CoA(A1,2)

2 representing the second segement.

The UDF would be something like

Public Function CoA(account as string, xyz) as number
Select Case xyz
Case 1
CoA = left(account, 3)
Case 2
CoA = Mid(account, 7, 3)
Case n
CoA = n
End Select
End Function

I'm just struggling with what I need to include for it to work.

Try this out:

Function CoA(ByVal CellText As String, _
ByVal TheIndex As Long, _
Optional Delimiter As String = "-" _
) As String
Dim SplitText As Variant
SplitText = Split(CellText, Delimiter)
CoA = SplitText(TheIndex - 1)
End Function

UDF to return text
 

On Jun 25, 7:27*am, smartin wrote:
Forgone wrote:
Hi all, I'm trying to figure out how to make a UDF obtain a string
from any given cell, split and return the value depending on what the
user asks for.

For example.

Cell value in A1 = 233-2330100-100102-101-99999999-999999
There is 6 segments to this string....

The idea was that I would enter in cell A2

=CoA(A1,2)

2 representing the second segement.

The UDF would be something like

Public Function CoA(account as string, xyz) as number
* Select Case xyz
* *Case 1
* * *CoA = left(account, 3)
* Case 2
* * CoA = Mid(account, 7, 3)
*Case n
* *CoA = n
End Select
End Function

I'm just struggling with what I need to include for it to work.

Try this out:

Function CoA(ByVal CellText As String, _
* * * * * * * ByVal TheIndex As Long, _
* * * * * * * Optional Delimiter As String = "-" _
* * * * * * * ) As String
* * *Dim SplitText As Variant
* * *SplitText = Split(CellText, Delimiter)
* * *CoA = SplitText(TheIndex - 1)
End Function

It's coming up with #Name?

I'm entering it into the worksheet as...... =CoA(A1088,1)
I used the Function Argument Display and it came up with the
following.....

CellText = "233-9999999-998104-999-99999999-999999"
TheIndex = 1
Delimiter = (left blank)

Result = ""

UDF to return text
 

Forgone wrote:
On Jun 25, 7:27 am, smartin wrote:
Forgone wrote:
Hi all, I'm trying to figure out how to make a UDF obtain a string
from any given cell, split and return the value depending on what the
user asks for.
For example.
Cell value in A1 = 233-2330100-100102-101-99999999-999999
There is 6 segments to this string....
The idea was that I would enter in cell A2
=CoA(A1,2)
2 representing the second segement.
The UDF would be something like
Public Function CoA(account as string, xyz) as number
Select Case xyz
Case 1
CoA = left(account, 3)
Case 2
CoA = Mid(account, 7, 3)
Case n
CoA = n
End Select
End Function
I'm just struggling with what I need to include for it to work.
Try this out:

Function CoA(ByVal CellText As String, _
ByVal TheIndex As Long, _
Optional Delimiter As String = "-" _
) As String
Dim SplitText As Variant
SplitText = Split(CellText, Delimiter)
CoA = SplitText(TheIndex - 1)
End Function

It's coming up with #Name?

I'm entering it into the worksheet as...... =CoA(A1088,1)
I used the Function Argument Display and it came up with the
following.....

CellText = "233-9999999-998104-999-99999999-999999"
TheIndex = 1
Delimiter = (left blank)

Result = ""

Make sure the function code is in a module of your workbook. In VBE,
right click any part within VBAProject(Your Book Name), Insert, Module,
and put the code there.

UDF to return text
 

On Jun 25, 10:53*am, smartin wrote:
Forgone wrote:
On Jun 25, 7:27 am, smartin wrote:
Forgone wrote:
Hi all, I'm trying to figure out how to make a UDF obtain a string
from any given cell, split and return the value depending on what the
user asks for.
For example.
Cell value in A1 = 233-2330100-100102-101-99999999-999999
There is 6 segments to this string....
The idea was that I would enter in cell A2
=CoA(A1,2)
2 representing the second segement.
The UDF would be something like
Public Function CoA(account as string, xyz) as number
* Select Case xyz
* *Case 1
* * *CoA = left(account, 3)
* Case 2
* * CoA = Mid(account, 7, 3)
*Case n
* *CoA = n
End Select
End Function
I'm just struggling with what I need to include for it to work.
Try this out:

Function CoA(ByVal CellText As String, _
* * * * * * * ByVal TheIndex As Long, _
* * * * * * * Optional Delimiter As String = "-" _
* * * * * * * ) As String
* * *Dim SplitText As Variant
* * *SplitText = Split(CellText, Delimiter)
* * *CoA = SplitText(TheIndex - 1)
End Function

It's coming up with #Name?

I'm entering it into the worksheet as...... =CoA(A1088,1)
I used the Function Argument Display and it came up with the
following.....

CellText = "233-9999999-998104-999-99999999-999999"
TheIndex = 1
Delimiter = (left blank)

Result = ""

Make sure the function code is in a module of your workbook. In VBE,
right click any part within VBAProject(Your Book Name), Insert, Module,
and put the code there.

thanks, I had it in the personal.xls when it wasn't working, but I
just put it in another workbook and it worked.....