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

Question

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?

Answer 1

try:

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

Answer 2

Explanation:

• Use REGEXMATCH to check whether the value contains mb or gb.
• Use LEFT to remove the last two characters in the value (mb/gb).
• Use IFS to divide by 1000 or not depending on the original value and the output column.

Formula:

={{"Size in GB","Size in MB"};
  ARRAYFORMULA(IFERROR({
    IFS(REGEXMATCH(A2:A, "mb"), LEFT(A2:A,LEN(A2:A)-2)/1000,REGEXMATCH(A2:A, "gb"), LEFT(A2:A,LEN(A2:A)-2)),
    IFS(REGEXMATCH(A2:A, "gb"), LEFT(A2:A,LEN(A2:A)-2)*1000,REGEXMATCH(A2:A, "mb"), LEFT(A2:A,LEN(A2:A)-2))
  }))
}