Need a user defined function/macro to separate data in a cell

[bobby_smith]

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
Original Data	Function 1	Function 2
TD 5678 TEXT TEXT	TEXT TEXT
TI 76 TEXT TEXT TEXT TEXT	TEXT TEXT TEXT TEXT
NT 254 TEXT TEXT TEXT	TEXT TEXT TEXT

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]5678[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]76[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]254[/TD]

</tbody>

Sheet3​

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.

 

[FDibbins]

These formulae should do what you want...
[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][/tr]
[tr][td]

2​

[/td][td]Original Data[/td][td]Function 1[/td][td]Function 2[/td][/tr]

[tr][td]

3​

[/td][td]TD 5678 TEXT TEXT[/td][td]5678[/td][td]TEXT TEXT[/td][/tr]

[tr][td]

4​

[/td][td]TI 76 TEXT TEXT TEXT TEXT[/td][td]76[/td][td]TEXT TEXT TEXT TEXT[/td][/tr]

[tr][td]

5​

[/td][td]NT 254 TEXT TEXT TEXT[/td][td]254[/td][td]TEXT TEXT TEXT[/td][/tr]
[/table]
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)
both copied down

You could add =if(A3="","", at the start, if needed

 

[bobby_smith]

Thank you FDibbins for replying to my post. The formula works as required perfectly.
IF possible, can you write it as VBA user defined function.
I've attempted converting it to a function, but I've not been able to code the section that contains the Find function.
From what I'm reading the find function in vba has a different syntax to the excel find, and I'm not getting it to work.

Any help would be much appreciated even if its only the find function.

 

[bobby_smith]

Solution:
For anyone with the same problem, I was able to get my desired solution from hicker95:http://www.mrexcel.com/forum/excel-...fined-function-separate-data.html#post4121267

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