Formula to convert KG MB GB TB to GB in excel

0 votes

I'm attempting to convert data from an excel sheet that is stored in GB, MB, TB, and KB units. Everything needs to be changed to GB.

Here is the formula I use:

=IF(ISERROR(FIND("GB",H2))=FALSE,VALUE(LEFT(H2,FIND("GB",H2)-1)*1),IF(ISERROR(FIND("TB",H2))=FALSE,VALUE(LEFT(H2,FIND("TB",H2)-1)*1024),VALUE(LEFT(H2,FIND("MB",H2)-1)/1024),VALUE(LEFT(H2,FIND("KB",H2)-1)/1048576)))

I get an error with too many arguments.

Oct 31, 2022 in Others by Kithuzzz
• 38,020 points
2,349 views

1 answer to this question.

0 votes

Use this array formula:

=LEFT(A1,MIN(IFERROR(FIND({"KB","MB","GB","TB"},A1),1E+99))-1)/INDEX({1048576,1024,1,0.0009765625},MATCH(RIGHT(A1,2),{"KB","MB","GB","TB"},0)) & "GB"

Being an array formula it needs to be confirmed with Ctrl-Shift-Enter instead of enter. If done correctly then excel will put {} around the formula.

enter image description here


If your string always ends with the size then you can use this normal formula:

=LEFT(A1,LEN(A1)-2)/INDEX({1048576,1024,1,0.0009765625},MATCH(RIGHT(A1,2),{"KB","MB","GB","TB"},0)) & "GB"

I hope this helps you. 

answered Oct 31, 2022 by narikkadan
• 63,720 points

Related Questions In Others

0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

Feb 17, 2022 in Others by Edureka
• 13,690 points
928 views
0 votes
1 answer

Convert column in excel date format (DDDDD.tttt) to datetime using pandas

Given # s = df['date'] s 0 ...READ MORE

answered Oct 2, 2022 in Others by narikkadan
• 63,720 points
3,362 views
0 votes
1 answer

Convert PDF to Excel in Java

You can convert a PDF document to ...READ MORE

answered Oct 9, 2022 in Others by narikkadan
• 63,720 points
2,120 views
0 votes
1 answer

Excel Formula to convert from cm to inches and feet & round off to two digits

Use the TEXT function to format, and ...READ MORE

answered Oct 15, 2022 in Others by narikkadan
• 63,720 points
819 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
1,090 views
0 votes
1 answer

ImportError: openpyxl is required for loading excel format files

Forget the PsychoPy complications for the time ...READ MORE

answered Oct 3, 2018 in Python by Priyaj
• 58,100 points
1,017 views
0 votes
1 answer

In Blue Prism how to split excel column data into TWO columns

This is how I am doing it. Dim ...READ MORE

answered Oct 15, 2018 in RPA by Priyaj
• 58,100 points
4,294 views
0 votes
1 answer

Excel Power Query: Using List.MatchAny on a column value

try this. let TableA = ...READ MORE

answered Oct 22, 2018 in Power BI by Annie97
• 2,160 points
4,169 views
0 votes
1 answer

Convert image (jpg) to base64 in Excel VBA?

Heres a function. Can't remember where I ...READ MORE

answered Sep 27, 2022 in Others by narikkadan
• 63,720 points
2,859 views
0 votes
1 answer

How to convert PDF to Excel in C#?

Solutions a) Cometdocs makes a free online conversion from PDF ...READ MORE

answered Oct 1, 2022 in Others by narikkadan
• 63,720 points
886 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP