How to convert MB, KB, GB to bytes in excel

[neil28300]

Hi Team,

I am working with large spreadsheets and I would like to be able to standardize all the measurements in one of my excel column to Bytes - I have a data in below format -

Size

26.4 kB

25.27 MB

I have 100,00 rows with these values and want to get all of them converted to Bytes. Does anyone know, how to achieve this ?

Regards-

 

[arthurbr]

Internet does ) Convert Bytes to MB or GB in Excel (2 Easy Ways) - Spreadsheet Planet

 

[neil28300]

Internet does ) Convert Bytes to MB or GB in Excel (2 Easy Ways) - Spreadsheet Planet

Hi arthurbr,

Thank you, I did look at the link; - does it mean, I will need to filter the column to "KB", "GB" respectively and then use Convert formula ? I was thinking if it can be done with single formula, as I am working with 100,000 rows and have multiple columns.

Cheers !

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Maybe

+Fluff 1.xlsm
	A	B
1
2	26.4 kB	27,033.60
3	25.27 MB	26,497,515.52
4	10 GB	10,737,418,240.00
Main

Cell Formulas
Range	Formula
B2	B2	=SUM(LEFT(A2,FIND(" ",A2))*COUNTIFS(A2,{"*K*","*M*","*G*"})*({1024,1048576,1073741824}))
B3:B4	B3	=SUM(LEFT(A3,FIND(" ",A3))*COUNTIFS(A3,{"*k*","*M*","*G*"})*({1024,1048576,1073741824}))

 

[neil28300]

Thank you so so much, it worked and solved my query.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[neil28300]

You're welcome & thanks for the feedback.

I am sorry, but I forgot to mention that many rows are populated with "bytes" unit already, so, is there is way that these values remain as it is and it only changes "kB", "MB, "GB" as below -

Thanks in advance !

Size	Desired Values
10 bytes	10
3.24 kB	3317.76
2.5 MB	2621440
1.1 GB	1181116006

 

[shaowu459]

I am sorry, but I forgot to mention that many rows are populated with "bytes" unit already, so, is there is way that these values remain as it is and it only changes "kB", "MB, "GB" as below -

Thanks in advance !

Size	Desired Values
10 bytes	10
3.24 kB	3317.76
2.5 MB	2621440
1.1 GB	1181116006

you just need make a little change on Fluff's solution:

工作簿1
	A	B	C
1
2	10 bytes	10
3	3.24 kB	3317.76
4	2.5 MB	2621440
5	1.1 GB	1181116006
6
7
Sheet1

Cell Formulas
Range	Formula
B2:B5	B2	=SUM(LEFT(A2,FIND(" ",A2))*COUNTIFS(A2,{"*y*","*K*","*M*","*G*"})*1024^{0,1,2,3})

 

[neil28300]

Thank you so so much, I am all set now. I really appreciate your help.