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.
Solved! Go to Solution.
Hey @vsalver
Try this formula:
If REGEX_CountMatches([Brokers], "\d+?") = Length(Trim([Brokers])) Then "B"+[Brokers] Else [Brokers] Endif
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
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)
If you've got spaces ....
IIF(Regex_Match([Field1],"\s*\d+\s*"),"B"+trim([Field1]),[Field1])
Cheers,
Mark