# EXcel return part of a string with VBA



## rlovell6 (Apr 9, 2005)

I have cells with data in the following format:

TX_TGIF_DATA.05_56-9 Unit

I need VB code to pull out the number in the middle. It will always be preceded by a period and followed by a space but will have a differing number of characters. I need the number in the middle returned in a string for me to use later in the code.

Thanks, I appreciate any help or suggestions.


----------



## Glaswegian (Sep 16, 2005)

Hi

I was struggling to come up with a formula for this, so after some research this UDF should do the trick.

```
Function extractno(cell As Range)
    Dim no() As String
    Dim vread As String
    ReDim Preserve no(2)
    vread = cell.Value
    no = Split(vread, ".")
    vread = no(1)
    no = Split(vread, " ")
    extractno = no(0)
End Function
```
To use, input this in cell A2 if your data is in cell A1, and so on

*=extractno(A1)*


----------



## rlovell6 (Apr 9, 2005)

Thanks for your help. I was able to find a solution (below) but I will try yours as well.

Function GetNum(Text As Variant) As String 
Dim strTemp As String 

strTemp = Split(Text, " ")(0) 
GetNum = Mid(strTemp, InStr(strTemp, ".") + 1) 

End Function 


Thanks Again!


----------



## Glaswegian (Sep 16, 2005)

Nice one - pretty much the same, but perhaps more efficient. :grin:


----------

