Spilt data into two columns

A

Alam

Function GetOt(rng As Variant, Noh As Variant, Amount As Variant) As Variant
If rng Like "HRS" Then
GetOt = (Noh) & "-" & (Amount)
Else
GetOt = ""
End If
End Function

The result is:
DESC NOD/NOH AMOUNT RESULT
DYS 31.00 550.00
HRS 60.00 150.75 60-150.75
DYS 10.00 460.30
DYS 10.00 115.10
DYS 31.00 450.00
HRS 60.00 123.00 60-123
DYS 31.00 450.00
HRS 80.00 164.00 80-164
HRS 9.00 22.14 9-22.14
DYS 31.00 950.00
HRS 105.00 455.44 105-455.44
HRS 23.00 119.72 23-119.72
DYS 31.00 1,000.00
HRS 89.00 406.06 89-406.06
HRS 23.00 125.92 23-125.92
This the is the result of my macro, but instead to segregate my result by(
Data -text to column,) I want spilt this result into two columns directly by
macro. Please I f any one can help.
Thanks
 
B

Brian Herbert Withun

Function GetOt(rng As Variant, Noh As Variant, Amount As Variant) As Variant
If rng Like "HRS" Then
GetOt = (Noh) & "-" & (Amount)
Else
GetOt = ""
End If
End Function

The result is:
DESC NOD/NOH AMOUNT RESULT
DYS 31.00 550.00
HRS 60.00 150.75 60-150.75
DYS 10.00 460.30
DYS 10.00 115.10
DYS 31.00 450.00
HRS 60.00 123.00 60-123
DYS 31.00 450.00
HRS 80.00 164.00 80-164
HRS 9.00 22.14 9-22.14
DYS 31.00 950.00
HRS 105.00 455.44 105-455.44
HRS 23.00 119.72 23-119.72
DYS 31.00 1,000.00
HRS 89.00 406.06 89-406.06
HRS 23.00 125.92 23-125.92
This the is the result of my macro, but instead to segregate my result by(
Data -text to column,) I want spilt this result into two columns directly by
macro. Please I f any one can help.
Thanks
Click to expand...

Can you better describe your problem? I do not know what you are
trying to do with this function.

Brian Herbert Withun
 
J

Joel

If your data is in column A (before text to column), then the function would
only have one input parameter and return only one value (functions only
return one value). this should be close to what you need. let me know if I
can make any additional changes.

call with
=GetOt(A1)

Function GetOt(rng As Variant) As Variant
If Left(rng, 3) = "HRS" Then
Noh = Trim(Mid(rng, 4))
Amount = Trim(Mid(Noh, InStr(Noh, " ")))
Amount = Trim(Left(Amount, InStr(Amount, " ") - 1))
Noh = Val(Trim(Left(Noh, InStr(Noh, " ") - 1)))

GetOt = Format(Noh, "0.00") & "-" & Format(Amount, "0.00")
Else
GetOt = ""
End If
End Function
 
Top