I have a column with data that looks like:
7-Day: 320 (5.6%)
I need to extract the 320 as a number. It is not always a 3 digit though. The value can range from 0 - 9999. And if it is 0, the percent is not calculated.
A larger block from the column:
7-Day: 0
7-Day: 320 (5.6%)
7-Day: 325 (5.5%)
7-Day: 472 (8.5%)
7-Day: 89 (2.5%)
7-Day: 258 (4.4%)
7-Day: 499 (8.7%)
7-Day: 143 (2.4%)
Thanks.
Try
=MID(A1,FIND(" ",A1)+1,3)+0
The formula takes the three characters following the first space, then adds a zero, which will coerce text into numbers and ignore spaces.
Copying the data sample from above into Excel, the spaces are non-breaking spaces (character 160), but I assume that in your spreadsheet you will have normal spaces. If your original data has non-breaking spaces, you can use
=SUBSTITUTE(MID(A3,FIND(" ",A3)+1,3),CHAR(160),"")+0
If your data always starts with the seven characters:
7-Day:
Then in B1 enter:
=IF(RIGHT(A1,1)="0",0,MID(A1,8,FIND(CHAR(160),A1)-8))
and copy down:
