# Alteryx Designer Discussions

The Expert Exam is now live online! Read about the specifics and what it took to bring it to life in the blog by our very own Elizabeth Bonnell!
SOLVED

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

Highlighted
8 - Asteroid

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

Highlighted
Alteryx

you could try this! hope this helps

Highlighted
8 - Asteroid

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.

Highlighted
Alteryx

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!

Labels