# Solved: Excel-VBA-Split string



## sk0101 (Nov 8, 2007)

Hi,

A1 Cell will be populated with the data (3 different ways) please see an example below 
I need to grab the numbers and display them in two different cells. 

i.e 
A1 = Frozen (-20 to 20°C) = Output: B1= -20, C1 = 20°C
or
A1 = Refrigerated (15 to 30°C) = Output: B1= 15, C1 = 30°C
or
A1 = Excessive heat (>10°C) = Output: B1= empty, C1 = >10°C


what would be a good way to get the numbers.. Some function that can be used in VBA code. 

Thank u


----------



## Zack Barresse (Jul 25, 2004)

Hi again,

Why do you need this in VBA? What are the actual cell values holding? Or do you want a formula in B1 and/or C1 which will show that type of output? What are all of your conditions? Do you think you could put together a table of conditions and their respective outputs? Can you post a sample file with some actual data and what you would like it to look like?


----------



## sk0101 (Nov 8, 2007)

Hi Zack, 

The reason I want in VBA is because I already have code in there and I want all to be in one place. 
I have attached an excel file and provided some examples. 

Please take a look at it.. 

I hope it is more clear now, 

Thank you for your help


----------



## slurpee55 (Oct 20, 2004)

I'm no coder, so this is more of an exercise that I am curious about.
But for those cells the following formulas work for the following values (only 2 so far)
Frozen (-20 to 20°C)
and
Refrigerated (15 to 30°C)
=IF(SEARCH("to",A1,1)>0,MID(A1,VALUE(SEARCH("(",A1,1)+1),3),"") returns either -20 or 15
and 
=IF(SEARCH("to",A1,1)>0,MID(A1,VALUE(SEARCH("to",A1,1)+2),5),"") returns either 20°C or 30°C.
However, I get an error on the other, given that SEARCH is not a value.
Still, maybe to get you started....


----------



## slurpee55 (Oct 20, 2004)

hmmm, this will find the value of 10°C
=IF(SEARCH("(",A3,1)>0,MID(A3,VALUE(SEARCH(")",A3,1)-4),4),"")
but there must be another search needed, probably based on looking for ">" in the cell


----------



## sk0101 (Nov 8, 2007)

Thanks slurpee55,
based on the formula..
ie, 
If I test Frozen (-150 to 11°C) ..the output displays -15 and 11 should be -150
or
if I test Frozen (-11 to 1°C) .. the output displays -11 and 1°C) "extra bracket"

I am working on it.. If I figure a good way I will post the result here. 

Also can I use the search function in vba code?

Thanks,


----------



## slurpee55 (Oct 20, 2004)

dunno about Search - I think SEEK does about the same thing, but I am not sure....


----------



## Zack Barresse (Jul 25, 2004)

Those values need to be split up. Why do you have them all in one cell? Will the number values ever change?

From your example file I would use three columns, and these worked for me...

Number to number:

```
=LEFT(TRIM(RIGHT(A4,LEN(A4)-FIND("(",A4,1))),LEN(TRIM(RIGHT(A4,LEN(A4)-FIND("(",A4,1))))-1)
```
First number w/ signs:

```
=IF(ISNUMBER(FIND(" to ",B4,1)),LEFT(B4,FIND(" to",B4,1)-1),"")
```
Second number w/ signs and unit of measurement:

```
=IF(ISNUMBER(FIND(" to ",B4,1)),RIGHT(B4,LEN(B4)-FIND(" to ",B4,1)-3),B4)
```
This gives results exactly as you've asked. Do you still want this in VBA?

HTH


----------



## sk0101 (Nov 8, 2007)

The numbers can be three digits (150), two digits (-25) or one digit (8)

One of them will be imported by some third-party software.. everytime it can different and I would need to split and get the digits.

ie. 
Frozen (-150 to 11°C) 
or
Frozen (-15 to 11°C) 
or 
Frozen (-1 to 8°C) 
or special condtion: Excessive heat (>10°C)


Yes please provide the VBA code if you can.

Thank u


----------



## Zack Barresse (Jul 25, 2004)

Well then, here are three UDFs for you then...


```
Option Explicit

Public Function BothNumbers(rngRef As Variant) As Variant
    Dim xVal As Variant, iStart As Long, iEnd As Long
    On Error GoTo ErrFunction
    BothNumbers = CVErr(xlErrNA)
    If TypeName(rngRef) = "Range" Then
        xVal = rngRef.Value
    Else
        xVal = rngRef
    End If
    iStart = InStr(1, xVal, "(") + 1
    iEnd = InStr(1, xVal, ")")
    BothNumbers = Mid(xVal, iStart, iEnd - iStart)
ErrFunction:
End Function

Public Function FirstNumber(rngRef As Variant) As Variant
    Dim xVal As Variant, iStart As Long, iEnd As Long, sNums As String
    On Error GoTo ErrFunction
    FirstNumber = CVErr(xlErrNA)
    If TypeName(rngRef) = "Range" Then
        xVal = rngRef.Value
    Else
        xVal = rngRef
    End If
    iStart = InStr(1, xVal, "(") + 1
    iEnd = InStr(1, xVal, ")")
    If InStr(1, xVal, " to ") = 0 Then
        FirstNumber = vbNullString
        Exit Function
    End If
    sNums = Mid(xVal, iStart, iEnd - iStart)
    FirstNumber = Left(sNums, InStr(1, sNums, " ") - 1)
ErrFunction:
End Function

Public Function SecondNumber(rngRef As Variant) As Variant
    Dim xVal As Variant, iStart As Long, iEnd As Long, sNums As String
    On Error GoTo ErrFunction
    SecondNumber = CVErr(xlErrNA)
    If TypeName(rngRef) = "Range" Then
        xVal = rngRef.Value
    Else
        xVal = rngRef
    End If
    iStart = InStr(1, xVal, "(") + 1
    iEnd = InStr(1, xVal, ")")
    If InStr(1, xVal, " to ") = 0 Then
        SecondNumber = vbNullString
        Exit Function
    End If
    sNums = Mid(xVal, iStart, iEnd - iStart)
    SecondNumber = Right(sNums, Len(sNums) - InStrRev(sNums, " "))
ErrFunction:
End Function
```
Call like so in a cell...

=BothNumbers(A4)
=FirstNumber(A4)
=SecondNumber(A4)

Call in VBE like so...


```
Msgbox BothNumbers("Frozen (-150 to 11°C)")
Msgbox FirstNumber("Frozen (-150 to 11°C)")
Msgbox SecondNumber("Frozen (-150 to 11°C)")
```


----------



## Keebellah (Mar 27, 2008)

Hi,
I am a VBA code fan.
I added a Function I found on the Internet (orquid...) and added 2 funtions lValue() and rValue() which extract the number from the cell value.
The "to" is the split I look for so left of the "to" string and right of it.

I did nog add the additional calculation for the - if it's below zero.
The example is your Excel sheet with the vba code appended to yours.

I named it sk0101-example.xls 
See if you can work it out

In the Sheet1 I used the functions and with conditional formatting I left the 0 values blank


----------



## sk0101 (Nov 8, 2007)

Hi Zack and Keebellah .. Both examples are perfect.. I want to thank both of you for your help,

Zack, 
-Could you please explain me why you declared xVal as Variant, and not as String..When Variant is used? 
-Also what does TypeName = Range means.. 'if TypeName(rngRef) = "Range" '

The reason I am asking, is because I changed the xVal to string type and commented the below condition and just left the: xVal = rngRef

'if TypeName(rngRef) = "Range" Then
* xVal = rngRef.Value*'
Else
' xVal = rngRef
'End If

and it worked.. I want to understand your script better, The Variant type and what is TypeName, for future references.

Thanks


----------



## Aj_old (Sep 24, 2007)

Hi to all
I wanna post a solution to, I used just excel standard functions:
in b4:

```
=IF(ISERROR(FIND(" to ";A4;FIND("(";A4)));IF(ISERROR(FIND(">";A4;FIND("(";A4)));MID(A4;FIND("<";A4;FIND("(";A4))+1;FIND(")";A4)-FIND("<";A4;FIND("(";A4))-1);"");MID(A4;FIND("(";A4)+1;FIND(" to ";A4)-FIND("(";A4)))
```
in C4:

```
=IF(ISERROR(FIND(" to ";A4;FIND("(";A4)));IF(ISERROR(FIND(">";A4;FIND("(";A4)));"";MID(A4;FIND(">";A4;FIND("(";A4))+1;FIND(")";A4)-FIND(">";A4;FIND("(";A4))-1));MID(A4;FIND(" to ";A4)+4;FIND(")";A4)-FIND(" to ";A4)-4))
```
They are pretty long, but work fine!
Frozen (-120 to 20°C) -120 20°C
Refrigerated (15 to 30°C) 15 30°C
Excessive heat (>10°C) 10°C
Excessive cold (< -10°C) -10°C	(if you'll have such situation it will work to)

HTH


----------



## Zack Barresse (Jul 25, 2004)

The reason I did that is so that you can use it in the worksheet as a function as well. If you just used *rnfRef.Value* it would error out if you used it in the VBIDE as you would not be passing a range (if strictly through the VBIDE and not a worksheet), but a value, i.e. a string value. This way you can use it in any case.

HTH


----------



## sk0101 (Nov 8, 2007)

Thanks Zack.. 
When you say a function, do you mean same as sub


----------



## Zack Barresse (Jul 25, 2004)

No, when I mean function, I mean function, not sub [routine]. There are two types of things you can use in VBA - sub routines and functions. So you'll see one of two things..

```
Sub TestingThis()
End Sub

Function MyFunction()
End Function
```
A function will return a value, whereas a sub [routine] will perform an action. That is the real difference. So what I posted were _functions_ as opposed to sub routines. Of course you could have used sub routines, but that would have been a little more hassle, because then you would have to deal with, okay, where is my activecell, where do I want the data, where should I look for the input data from where I currently am, is that file open, is that information available, etc. Functions have their places - put something in, get something out.


----------



## sk0101 (Nov 8, 2007)

Thank you Zack for the explanation


----------

