Coverting messy string to number with format like 1 × 10^6

[apoptoticbody]

Hi alteryx experts,

I am working on converting a list of strings to numbers, and i would populate these numbers into tableau for data analysis afterwards.

My list of strings is like this:

Dosage

2.5 X 10^7

0.2 x 10^7

2 1 x 10^8

3-8 x 10^9

1.0 x 10^12

2.5 x 10^8

1-5 x 10^8

1-3x10^7

5.0 x 10^6

5.0 x 10^6

5.0 x 10^6

5-1 x 10^6

1-10 x 10^6

225 x 10^6

5-4 x 10^8

1-1 x 10^6

1.5 x 10^6

9.0 x 10^6

1.5 x 10^8

2.00 x 10^8

4.00 x 10^8

9.0 x 10^6

9.0 x 10^6

2-6 x 10^6

In previous process, I parsed out these dosage information with RegEx and syntax so i guess the "x" sign is strings. I was wondering how i could do the following:

1) Make all the records into exact numbers, i.e., 2.00 × 10^8 into 200000000

2) if a record is a range, i.e., 2-6 × 10^6, wonder if there's a way to convert it to "2000000,6000000"?

I have looked up numerous posts but it seems that not many mention similar issue as i encountered. Sorry if this question is dumb but i have been thinking it for half week and could not get it.

Appreciated your help!!!

Best,

XO

[atcodedog05 · Accepted solution]

Hi @apoptoticbody ,

Here a workflow for the task.

Output as desired

Hope this helps : )

If solves your task please mark the post as solution

[vizAlter · Accepted solution]

Hi @apoptoticbody — Try this solution:

1st RegEx's expression is:

([.\d]+).*\^(\d+)

2nd RegEx's expression is:

-(\d+)

Please mark it "Solved" or "Solved" with a Like if it resolved your query. This will help other users find the same answer/resolution.  Thank you.

[apoptoticbody]

Hi @atcodedog05 , Sorry for my reply, thank you for your solutions and i have learned much through walking through the flow you provided. Thank you very much for your help!

[apoptoticbody]

Hi @vizAlter , thank you so so so much for this neat workflow! You are the hero! Thanks for your help!