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

[ibesmond]

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

[bpatel · Accepted solution]

@ibesmond ,

you could try this! hope this helps

[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.

[bpatel]

hi @ibesmond ,

you can use the transpose and cross tab to achieve this for multiple fields. for example if this was your starting data

you can build a workflow like this

to get this output

hope this helps!