How can I convert GB and MB data string to GB(number) in Google sheet?-CodePudding

Home > Mobile > How can I convert GB and MB data string to GB(number) in Google sheet?

How can I convert GB and MB data string to GB(number) in Google sheet?

Time：01-21

I have below data in string format

Size
670.2mb
668.4mb
3.2gb
1.1gb
479.7mb

I want to convert this column in below format

Sizw in GB    Size in MB
0.6544        670.2
0.6527        668.2
3.2           3276.8
1.1           1126.4
0.4684        479.7

how can I do that?

CodePudding user response：

Use

CodePudding user response：

try:

=INDEX(IFNA(REGEXEXTRACT(A2:A, "\d (?:\.\d )?")*
 VLOOKUP(REGEXEXTRACT(A2:A, "(?i)[a-z] $"), 
 {{"mb"; "gb"}, {1; 1024}}, {2, 2}, )/{1024, 1}))