Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 = "" |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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..... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return Text based upon text matching | Excel Worksheet Functions | |||
Lookup Text and Receive Other Text in Return | Excel Worksheet Functions | |||
Nested formula to search a text string and return specific text | Excel Worksheet Functions | |||
2 criteria lookup of text. Return text form column 3. SUMPRODUCT t | Excel Worksheet Functions | |||
Link to text and return text into a formula? | Excel Worksheet Functions |