Function to read the whole number

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.

Expression

Accepted answers

MarqueeCrew

@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

All comments

LordNeilLord

Hey @vsalver

Try this formula:

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

MarqueeCrew

@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)

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703