Pulling numeric values from cells

[jpencek3]

I need a single function or a macro that can pull out a number from a cell, when the cell can be written in 1 of 2 formats: # of # GB used (x%) or # GB of # GB used (x%). I need the 2nd #. Both numbers are variable length, both before and after the decimal, the letters GB can also be written as MB or KB, and I am having trouble getting text to columns to work via the macro.

or

 

[jasonb75]

With formulas, try these for the first and second numbers respectively.

=--LEFT(A2,SEARCH(" ",A2))
=--LEFT(MID(A2,FIND(" of ",A2)+4,LEN(A2)),SEARCH(" ",MID(A2,FIND(" of ",A2)+4,LEN(A2))))

 

[jpencek3]

With formulas, try these for the first and second numbers respectively.

=--LEFT(A2,SEARCH(" ",A2))
=--LEFT(MID(A2,FIND(" of ",A2)+4,LEN(A2)),SEARCH(" ",MID(A2,FIND(" of ",A2)+4,LEN(A2))))

This does show me the numbers, but I forgot to mention that I will then do calculations with them after the fact. When I use this formula, it doesn't show me the numbers as numeric so I can't do the calculation.. Any thoughts?

 

[jasonb75]

The -- at the start of the formula makes them numeric, if your formulas are not recognising them then it must be down to a different issue.
If they were not valid numbers then using -- would have given you a #VALUE! error instead of the numbers.

 

[Scott Huish]

A little shorter 2nd formula:

Book3
	A	B	C
1	460.981 GB of 690.721 GB used (67%)	460.981	690.721
2	267.017 of 582.939 GB used (46%)	267.017	582.939
Sheet1

Cell Formulas
Range	Formula
B1:B2	B1	=--LEFT(A1,FIND(" ",A1)-1)
C1:C2	C1	=--(LEFT(SUBSTITUTE(REPLACE(A1,1,FIND("of",A1)+2,"")," ",REPT(" ",255)),255))

 

[jpencek3]

Both formulas are giving me #VALUE!, unless I remove the --. The other thing that makes this so tough is that the number of spaces between everything doesn't seem consistent. A little background is that I am using UiPath RPA to read this data from a webpage and then paste it into Excel, and then am trying to pull out data to extrapolate and run some calculations with.

Is there any way to upload 2 sample spreadsheets so you guys can see what I'm talking about with these issues? I've tried all kinds of combinations of functions, text to columns, and find and replace, but can't get it to work in a macro (that is ultimately how it is going to be run... a macro writing in the function or doing whatever needs to be done)

 

[jasonb75]

With data being pulled from a web page irregular hidden characters are quite common which would most likely be the cause of the error, numbers and regular spaces would be correctly coerced by the double unary in the formula. If Scott's first formula is showing #VALUE! then that means that there are zero width characters between the digits which we would need to identify and remove.

You can attach samples by clicking the 'XL2BB' button on the reply toolbar and installing the add-in. If corporate restrictions prevent that then the best way would be to upload to a file share site and post a link to your file. The file share option might be better here in case the xl2bb software doesn't retain the hidden characters.

 

[jpencek3]

I made a (probably very inefficient) function that works for all the handful of cases that i currently have (there seems to be only 2 different amount of hidden characters).. i am going to test with other customers' data and if there is more inconsistencies i will upload to dropbox and share. THANK YOU GUYS SO MUCH FOR YOUR HELP!!!

=IF(ISNUMBER(--MID(A2,SEARCH("of",A2,1)+3,(SEARCH("used",A2,1)-13)-(SEARCH("of",A2,1))))=TRUE,--MID(A2,SEARCH("of",A2,1)+3,(SEARCH("used",A2,1)-13)-(SEARCH("of",A2,1))),--MID(A2,SEARCH("of",A2,1)+5,(SEARCH("used",A2,1)-13)-(SEARCH("of",A2,1))))

 

[Scott Huish]

Try this:

Book2
	A	B	C
1	460.981 GB of 690.721 GB used (67%)	460.981	690.721
2	267.017 of 582.939 GB used (46%)	267.017	582.939
Sheet1

Cell Formulas
Range	Formula
B1:B2	B1	=--LEFT(A1,FIND(" ",TRIM(SUBSTITUTE(A1,CHAR(160)," ")))-1)
C1:C2	C1	=--(LEFT(SUBSTITUTE(TRIM(SUBSTITUTE(REPLACE(A1,1,FIND("of",A1)+1,""),CHAR(160)," "))," ",REPT(" ",255)),255))