Been looking in the community for an answer to this. Maybe I'm not putting in the right search terms, so I'll ask the question. If there a way or formula to convert suffixed numbers to general number format? 1.2M12000001K10003.27B32700000005T50000000000002.2T2200000000000 It can be done in excel with this formula: IFNA(LEFT(H28,LEN(H28)-1)*CHOOSE(MATCH(RIGHT(H28,1), {"K","M","B","T"},0),1000,1000000,1000000000,1000000000000),H28) Thanks

Converting suffix number (K M B T) to general number format (long number)

Been looking in the community for an answer to this. Maybe I'm not putting in the right search terms, so I'll ask the question.

If there a way or formula to convert suffixed numbers to general number format?

1.2M	1200000
1K	1000
3.27B	3270000000
5T	5000000000000
2.2T	2200000000000

It can be done in excel with this formula:

IFNA(LEFT(H28,LEN(H28)-1)*CHOOSE(MATCH(RIGHT(H28,1), {"K","M","B","T"},0),1000,1000000,1000000000,1000000000000),H28)

Thanks

Developer

Best Practices

Accepted answers

bpatel

@ibesmond ,

you could try this! hope this helps

New Workflow6.yxmd

All comments

bpatel

@ibesmond ,

you could try this! hope this helps

New Workflow6.yxmd

ibesmond

That's a solid solution. Thank you. Is there a way this could be applied to multiple fields without having to add 5 regex tools, and maybe using a replace tool to cut down on the number of formulas?

This was my original solution. Its a long complicated if statement. It works because I know I have at most 1 decimal point.

IF contains([Revenue (USD)],"K") AND !Contains([Revenue (USD)],".")
THEN Replace([Revenue (USD)],"K","000")
ELSEIF contains([Revenue (USD)],"K") AND Contains([Revenue (USD)],".")
THEN (Replace([Revenue (USD)],"K","00") AND Replace([Revenue (USD)],".",""))
ELSEIF (Contains([Revenue (USD)], "M") AND !Contains([Revenue (USD)], "."))
THEN Replace([Revenue (USD)],"M","000000")
ELSEIF (Contains([Revenue (USD)], "M") AND Contains([Revenue (USD)], "."))
THEN (Replace([Revenue (USD)],"M","00000") AND Replace([Revenue (USD)],".",""))
ELSEIF (Contains([Revenue (USD)], "B") AND Contains([Revenue (USD)], "."))
THEN (Replace([Revenue (USD)],"B","00000000") AND Replace([Revenue (USD)],".",""))
ELSEIF (Contains([Revenue (USD)], "B") AND !Contains([Revenue (USD)], "."))
THEN Replace([Revenue (USD)],"B","000000000") ELSE
[Revenue (USD)] ENDIF

The formula is large and will break if introduced a value like 1.22M. The plus side is I can convert all 5 fields in one formula tool.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15880

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703