Help converting a formula into a user defined function to separate data

[bobby_smith]

I earlier requested assistance in creating two user define functions to separate my data as shown in the table below.
If you have an alternative or a more efficient function that would accomplish the same task, that will be ok.

Excel 2010

	A	B	C
1	Original Data	Function 1	Function 2
2	TD 5678 TEXT TEXT	5678	TEXT TEXT
3	TI 76 TEXT TEXT TEXT TEXT	76	TEXT TEXT TEXT TEXT
4	NT 254 TEXT TEXT TEXT	254	TEXT TEXT TEXT

<tbody>
</tbody>

(for those wondering why not use the text formulas such as LEFT, MID, RIGHT or the text to column feature, I've put the explanation at the end of the post)

Someone was kind enough to reply with formula that works perfectly. However, I'm attempting to write the formula as a user defined function and I keep getting errors. I think its because of the find function.

Here are the two formulas I would like to be written as a function

B3=LEFT(MID(A3,FIND(" ",A3,1)+1,99),FIND(" ",MID(A3,FIND(" ",A3,1)+1,99),1)-1)
C3=MID(A3,FIND(B3,A3,1)+LEN(B3)+1,99)

A3 will represent the cell that has the data I need. Here is my original post: I would like some assistance creating two separate “user defined functions” whereby when I select the data, it separates the data to either number or text. I’ve included the format that the data will normally be in. Also please note that i'm requesting a function and not a macro so that for example I can type in cell B2: =function1(A2) and get the results that are shown in cell B2 Excel 2010 A B C 1 Original Data Macro 1 Macro 2 2 TD 5678 TEXT TEXT 5678 TEXT TEXT 3 TI 76 TEXT TEXT TEXT TEXT 76 TEXT TEXT TEXT TEXT 4 NT 254 TEXT TEXT TEXT 254 TEXT TEXT TEXT <tbody> </tbody> ​ It will always be two letters,followed by numbers that can range from 1 digit to four digits, and then text that can be any length. I currently use the text features such as LEFT(), MID(), RIGHT(), but my results are inconsistent because both the numbers and text may vary in length. I’ve also tried the “Text to Column” feature, however the result is inconsistent because the text at the end varies in length. For example, if I have 200 lines of items of different length, the text to column feature would sometimes put data in adjacent cells overwriting information already in those cells What I need the function to do is, upon selecting the data 1) Only the numbers remain 2) Only the text after the numbers remain. Hence Why I would prefer two separate macros. I would really appreciate your help with this.

<tbody>
</tbody>

<tbody>
</tbody>

 

[hiker95]

bobby_smith,

How about something like this?

Excel 2007
	A	B	C
1	Original Data
2	TD 5678 TEXT TEXT	5678	TEXT TEXT
3	TI 76 TEXT TEXT TEXT TEXT	76	TEXT TEXT TEXT TEXT
4	NT 254 TEXT TEXT TEXT	254	TEXT TEXT TEXT
5
Sheet1

Cell Formulas
Range	Formula
B2	=0+TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",100)),100,100))
C2	=MID(A2,FIND(B2,A2,1)+LEN(B2)+1,99)

The formula in cell B2, copied down:
=0+TRIM(MID(SUBSTITUTE(A2," ",REPT(" ",100)),100,100))

The formula in cell C2, copied down:
=MID(A2,FIND(B2,A2,1)+LEN(B2)+1,99)

 

[bobby_smith]

hiker95, thanks for taking the time to respond.

The formulas need to be independent of each other. I note the second formula is including the results from the first. The layout I gave was just an example as my results would not be in adjacent cells.
I need to be able to use it anywhere in the, and I need to be able to use the second formula without having to use the first formula.

The formulas below, gives the exact results that I need.
=LEFT(MID(A3,FIND(" ",A3,1)+1,99),FIND(" ",MID(A3,FIND(" ",A3,1)+1,99),1)-1)
=MID(A3,FIND(B3,A3,1)+LEN(B3)+1,99)

What I need some help with is converting the formulas above to two custom function (user defined function) so I can use it in any workbook without having to copy and paste or typing the formula each time i need it.

Thanks.


Thanks.

 

[hiker95]

bobby_smith,

Here are two custom/user defined functions (with instructions in the code) for you to consider, based on the raw data you have displayed:

Excel 2007
	A	B	C
1	Original Data	ExtractNumber Function	ExtractText Function
2	TD 5678 TEXT TEXT	5678	TEXT TEXT
3	TI 76 TEXT TEXT TEXT TEXT	76	TEXT TEXT TEXT TEXT
4	NT 254 TEXT TEXT TEXT	254	TEXT TEXT TEXT
5
Sheet1

Cell Formulas
Range	Formula
B2	=ExtractNumber(A2)
C2	=ExtractText(A2)

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below two custom/user defined functions
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. Then follow instructions in the code for the two custom/user defined functions

Function ExtractNumber(NbrText As Range) As Long
' hiker95, 04/05/2015, ME846714
' If A2 contains "TD 5678 TEXT TEXT" without the " characters
' In B2 enter "=ExtractNumber(A2)" without the " characters
' B2 will display: 5678
Dim t As String, s, i As Long
s = Split([NbrText].Value, " ")
For i = LBound(s) To UBound(s)
  If IsNumeric(s(i)) Then
    ExtractNumber = s(i)
    Exit For
  End If
Next i
End Function


Function ExtractText(NbrText As Range) As String
' hiker95, 04/05/2015, ME846714
' If A2 contains "TD 5678 TEXT TEXT" without the " characters
' In C2 enter "=ExtractText(A2)" without the " characters
' C2 will display: TEXT TEXT
Dim t As String, s, i As Long
s = Split([NbrText].Value, " ")
For i = LBound(s) + 2 To UBound(s)
  t = t & s(i) & " "
Next i
If Right(t, 1) = " " Then
  t = Left(t, Len(t) - 1)
End If
ExtractText = t
End Function

Before you use the two custom/user defined functions with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then follow the instructions within the two custom/user defined functions.

 

[bobby_smith]

hiker95,

Thank you very much for taking the time to design this user function.
It works perfectly in accomplishing the task.
I'm truly grateful for you kindness.

 

[hiker95]

bobby_smith

Thanks for the feedback, and, the Private Message.

You are very welcome. Glad I could help.

And, come back anytime.

 

[hiker95]

bobby_smith,

Thanks for todays Private Message.

Here is one custom/user defined function (with instructions in the code) for you to consider, based on the new raw data in your Private Message:

New sample raw data, and, results:

Excel 2007
	A	B	C
1	Original Data	ExtractNumber Function	ExtractTextV2 Function
2	5678 TEXT TEXT	5678	TEXT TEXT
3	76 TEXT TEXT TEXT TEXT	76	TEXT TEXT TEXT TEXT
4	254 TEXT TEXT TEXT	254	TEXT TEXT TEXT
5
Sheet2

Cell Formulas
Range	Formula
B2	=ExtractNumber(A2)
C2	=ExtractTextV2(A2)

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below new custom/user defined function
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Function ExtractTextV2(NbrText As Range) As String
' hiker95, 04/25/2015, ME846714
' If A2 contains "5678 TEXT TEXT" without the " characters
' In C2 enter "=ExtractTextV2(A2)" without the " characters
' C2 will display: TEXT TEXT
Dim t As String, s, i As Long
s = Split([NbrText].Value, " ")
For i = LBound(s) + 1 To UBound(s)
  t = t & s(i) & " "
Next i
If Right(t, 1) = " " Then
  t = Left(t, Len(t) - 1)
End If
ExtractTextV2 = t
End Function

Before you use the new custom/user defined function with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm, and, answer the "do you want to enable macros" question as "yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

Then follow the instructions within the new custom/user defined function.

 

[bobby_smith]

Thank you, thank you once again Hiker95 for your continued assistance.
The function works as intended. I'm truly appreciative of your dedication to helping folks like me.

 

[hiker95]

bobby_smith,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.

 

[Rick Rothstein]

Function ExtractTextV2(NbrText As Range) As String
' hiker95, 04/25/2015, ME846714
' If A2 contains "5678 TEXT TEXT" without the " characters
' In C2 enter "=ExtractTextV2(A2)" without the " characters
' C2 will display: TEXT TEXT
Dim t As String, s, i As Long
s = Split([NbrText].Value, " ")
For i = LBound(s) + 1 To UBound(s)
  t = t & s(i) & " "
Next i
If Right(t, 1) = " " Then
  t = Left(t, Len(t) - 1)
End If
ExtractTextV2 = t
End Function

First, why did you include the square brackets that I highlighted in red above (NbrText is declared as a Range, so it has a Value property)?

Second, there is a optional third argument in the Split function that allows you to eliminate the loop and obtain the desired text immediately using a single line of code...

Function ExtractTextV2(NbrText As Range) As String
  ExtractTextV2 = Split(NbrText.Value & " ", " ", 2)(1)
End Function