Separating Alpha and Numeric Characters

**genoa** · 08-22-2015, 03:58 AM

Hello Everybody,

I have a problem and any help would be much appreciated.

Cell A2: Houses of Parliament London England 27 Eiffel Tower 115 Washington 8

or any combination: there can be one to five words before each number and
the numbers may be from 1 - 999.

What I would like is:

Cell B2: Houses of Parliament London England 27
Cell C2: Eiffel Tower 115
Cell D2: Washington 8

Many thanks,

James

**Glenn Kennedy** · 08-22-2015, 04:12 AM

Here's one way. There may be neater ways, though. These are array formulae. Array Formulae are a little different from ordinary formulas in that they MUST be confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER.

You will know the array is active when you see curly braces { } appear around the outside of your formula. If you do not CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again. Don't type the curly braces yourself - it won't work...

B2:=LEFT(A2,FIND(" ",A2,MIN(IFERROR(SEARCH({0,1,2,3,4,5,6,7,8,9},A2),""))))

C2:=LEFT(SUBSTITUTE(A2,B2,""),FIND(" ",SUBSTITUTE(A2,B2,""),MIN(IFERROR(SEARCH({0,1,2,3,4,5,6,7,8,9},SUBSTITUTE(A2,B2,"")),""))))

D2: =SUBSTITUTE(SUBSTITUTE(A2,B2,""),C2,"")