Function to read the whole number

[vsalver]

here is my formula I'm using to add the prefix B if the value is numeric. But it is adding the prefix it is varchar also. What formula I need to use to just add the prefix to numeric value

IF IsInteger([broker]) then "B"+[broker] else [broker] endif

Brokers              Expected results

006578                  B006578

548979                  B548979

1AD55                   1AD55

AAAD                    AAAD

If you look at the above example, I'm expecting the prefix just for the first two rows. Please advise what formula I need to use.

[LordNeilLord]

Hey @vsalver

Try this formula:

If REGEX_CountMatches([Brokers], "\d+?") = Length(Trim([Brokers])) 
Then "B"+[Brokers] Else [Brokers] Endif

[MarqueeCrew · Accepted solution]

@vsalver,

I apologize for giving you the following solution.  I see your quest as a pattern challenge.  If you see numbers only, then you want to create B as a prefix to those numbers otherwise keep the string.

IIF(Regex_Match([Field1],"\d+"),"B"+[Field1],[Field1])

The IsInteger() function is better suited for examination of numeric values than as strings of numbers.

Cheers,

Mark

[LordNeilLord]

@MarqueeCrew

Does that work when you have leading or trailing spaces? I ended up with them when I copied across and they seemed to throw my regex off (i must get better at the regex)

[MarqueeCrew]

@LordNeilLord,

If you've got spaces ....

IIF(Regex_Match([Field1],"\s*\d+\s*"),"B"+trim([Field1]),[Field1])

Cheers,

Mark