+8Hi all,
I import CSV files with salary ranges.
There are two fields from CSV: “Salary Min” & “Salary Max”. Their format is $75,000.00.
I want to have them in one field ($75,000 - $100,000), so created a third field with the formula
However, I get “$75000 - $100000/yr”.
I looked around and tried AT’s AI but so far luck.
Is there a way I can resolve this?
Thanks a lot
Best answer by TheTimeSavingCo
Try this:
IF(
AND({Salary Min}, {Salary Max}),
"$" &
REGEX_REPLACE( REGEX_REPLACE( REGEX_REPLACE( REGEX_REPLACE( REGEX_REPLACE( REGEX_REPLACE(ROUND({Salary Min})&"", "(\\d{3})$", ",$1"), "(\\d*)(\\d{3}),", "$1,$2," ), "(\\d{1,3})(\\d{3}),", ",$1,$2," ), "(\\d{1,3}),(\\d{3}),", "$1,$2," ), "^(\\d{1,3})(\\d{3}),", "$1,$2," ), "^,", "" ) & " "
&
" - $" &
REGEX_REPLACE( REGEX_REPLACE( REGEX_REPLACE( REGEX_REPLACE( REGEX_REPLACE( REGEX_REPLACE(ROUND({Salary Max})&"", "(\\d{3})$", ",$1"), "(\\d*)(\\d{3}),", "$1,$2," ), "(\\d{1,3})(\\d{3}),", ",$1,$2," ), "(\\d{1,3}),(\\d{3}),", "$1,$2," ), "^(\\d{1,3})(\\d{3}),", "$1,$2," ), "^,", "" ) &
"/yr"
)
Credit to
+32Try this:
IF(
AND({Salary Min}, {Salary Max}),
"$" &
REGEX_REPLACE( REGEX_REPLACE( REGEX_REPLACE( REGEX_REPLACE( REGEX_REPLACE( REGEX_REPLACE(ROUND({Salary Min})&"", "(\\d{3})$", ",$1"), "(\\d*)(\\d{3}),", "$1,$2," ), "(\\d{1,3})(\\d{3}),", ",$1,$2," ), "(\\d{1,3}),(\\d{3}),", "$1,$2," ), "^(\\d{1,3})(\\d{3}),", "$1,$2," ), "^,", "" ) & " "
&
" - $" &
REGEX_REPLACE( REGEX_REPLACE( REGEX_REPLACE( REGEX_REPLACE( REGEX_REPLACE( REGEX_REPLACE(ROUND({Salary Max})&"", "(\\d{3})$", ",$1"), "(\\d*)(\\d{3}),", "$1,$2," ), "(\\d{1,3})(\\d{3}),", ",$1,$2," ), "(\\d{1,3}),(\\d{3}),", "$1,$2," ), "^(\\d{1,3})(\\d{3}),", "$1,$2," ), "^,", "" ) &
"/yr"
)
Credit to
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.