Substring a money value

Question

i'm trying to substring a value (money 1000.99). I want to take everything before the .99 and the take everything after the 1000. also the value could be anything ie 100.00, 1100.00

I have tried this but not quite working

%%[
SET @NCBestPrice1 = NCBestPrice1
SET @NCBestPrice1Fix = FormatNumber(@NCBestPrice1fix,"F2")
SET @NCBestPrice1Pounds = Substring(@NCBestPrice1fix, 0, Subtract(Length(@NCBestPrice1fix),3))
SET @NCBestPrice1Pence = Substring(@NCBestPrice1fix,Add(Length(@NCBestPrice1Pounds),1), 3)
]%%

Getting this error:

Unable to generate preview A scripting error has occurred. An error occurred when attempting to evaluate a FormatNumber function call. The input value () cannot be formatted as a number. The first parameter value for a FormatNumber call must be a numeric type supported by the requested format. Value: Format: F2

The Data extension field is Text(100).

Any help is appreciated.

Answer 1

You might be better served by the IndexOf() function.

%%[
var @NCBestPrice1, @NCBestPrice1Fix, @NCBestPrice1Pounds, @NCBestPrice1Pence
SET @NCBestPrice1 = "1000"
SET @NCBestPrice1Fix = FormatNumber(@NCBestPrice1,"F2")
SET @NCBestPrice1Pounds = Substring(@NCBestPrice1fix, 0, Subtract(indexOf(@NCBestPrice1Fix,"."),1))
SET @NCBestPrice1Pence = Substring(@NCBestPrice1fix,IndexOf(@NCBestPrice1Fix,"."),length(@NCBestPrice1))

]%%
<br>@NCBestPrice1: %%=v(@NCBestPrice1)=%%
<br>@NCBestPrice1Fix: %%=v(@NCBestPrice1Fix)=%%
<br>@NCBestPrice1Pounds: %%=v(@NCBestPrice1Pounds)=%%
<br>@NCBestPrice1Pence: %%=v(@NCBestPrice1Pence)=%%

Output

@NCBestPrice1: 1000 
@NCBestPrice1Fix: 1000.00 
@NCBestPrice1Pounds: 1000 
@NCBestPrice1Pence: .00 

With an alternate input:

%%[
var @NCBestPrice1, @NCBestPrice1Fix, @NCBestPrice1Pounds, @NCBestPrice1Pence
SET @NCBestPrice1 = "100.99"
SET @NCBestPrice1Fix = FormatNumber(@NCBestPrice1,"F2")
SET @NCBestPrice1Pounds = Substring(@NCBestPrice1fix, 0, Subtract(indexOf(@NCBestPrice1Fix,"."),1))
SET @NCBestPrice1Pence = Substring(@NCBestPrice1fix,IndexOf(@NCBestPrice1Fix,"."),length(@NCBestPrice1))

]%%
<br>@NCBestPrice1: %%=v(@NCBestPrice1)=%%
<br>@NCBestPrice1Fix: %%=v(@NCBestPrice1Fix)=%%
<br>@NCBestPrice1Pounds: %%=v(@NCBestPrice1Pounds)=%%
<br>@NCBestPrice1Pence: %%=v(@NCBestPrice1Pence)=%%

Output

@NCBestPrice1: 100.99 
@NCBestPrice1Fix: 100.99 
@NCBestPrice1Pounds: 100 
@NCBestPrice1Pence: .99