Number Output

[wchillman]

So, I have many cells that contain a number and a word. Is the any function in Excel that will just output the number only. For example, in a cell that contains "100% Buy", I just want the 100% and not the Buy. Any ideas?

[ajryan88]

I believe that you have two options here, depending on how your data is formatted. Suppose cell A1 contains the text "100% Buy".

Option 1

If the formatting is consistently such that you want everything before the space and nothing after it, then you could use the following simple formula:

Please Login or Register  to view this content.

This will find the location of the space in the text, and return everything before (not including) the space.

Option 2

If the number could be before or after the space (or even somewhere in the middle of the text), then it's a bit more complicated. However, this should do the trick:

Please Login or Register  to view this content.

This formula should be entered as an array formula, using Ctrl + Shift + Enter.

To see what's happening here, I will break this formula down a bit for you. First, lets look at this part of the formula

Please Login or Register  to view this content.

This part of the formula is breaking down the contents of cell A1 into a (virtual) array of text strings of length 1. Note that all characters in the resulting array are considered text, so the number 1 is actually considered the string "1". Multiplying by 1 at the beginning of this expression overcomes this.
The next thing taking place in the formula is

Please Login or Register  to view this content.

The ISNUMBER function returns "TRUE" if the parameter is a number, and "FALSE" otherwise (as you may have guessed). By placing this in a MATCH function, and attempting to match the value of "TRUE", the value that is returned will be the position of the first character in the text string in cell A1 that is a number.
The second last thing to consider here is

Please Login or Register  to view this content.

The COUNT function returns the number of single-character strings in the virtual array that contain numbers.
Now by encompassing all of the above inside the MID function, what is returned is a substring of the original string in A1, starting at the position of the first number, and with a length equal to the number of numbers in the string.

NB: This will only work if all of the numbers appear together. This will also not return the "%" sign that you requested.

Some suggestions:
1. If you want a "%" sign at the end of each of the numbers, then use the CONCATENATE function or the following formula (simpler):

Please Login or Register  to view this content.

Note the only thing that has changed here is the '&"%"' at the end of the formula.

2. If you have numbers scattered throughout the string in the target cell, then perhaps it's time to consider using VBA. Let me know if this is the case and I will help you out with a solution, but for now I have tried to keep the solution VBA-free, although it may seem a little bit messy.

[elayaz]

Hi Try this array formula. Should be confirmed with Ctrl+Shift+Enter.

It extracts number and special characters, between the text.

Please Login or Register  to view this content.

[martindwilson]

if it can be xxx yyyy 100% zzz wwww
or 20% bbbb
or ffff 100%
then
=TRIM(MID(SUBSTITUTE(A1," ",REPT(" ",20)),MAX(FIND("%",SUBSTITUTE(A1," ",REPT(" ",20)))-10,1),11))