Separate Text & Number

Try this...

Data Range

	A	B	C
2	Wells 123	Wells	123
3	Wells 1234	Wells	1234
4	Wells Fargo 123	Wells Fargo	123
5	Wells Fargo 1234	Wells Fargo	1234
6	Wells Fargo Inc 123	Wells Fargo Inc	123
7	Wells Fargo Inc 1234	Wells Fargo Inc	1234

Enter this formula in C2:

=--TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",255)),255))

Enter this formula in B2:

=TRIM(SUBSTITUTE(A2,C2,""))

Select B2:C2 and copy down as needed.

this should work with all strings

=REPLACE(A1,1,MIN(SEARCH({0,1,2,3,4,5,6,7,8,9},A1&"0123456789"))-1,"")

	A	B
1	wells fargo 123	123
2	wells fargo 1234	1234
3	wells fargo inc 123 456 789	123 456 789
4	wells fargo 98 999 22	98 999 22
5	Wells 123	123
6	Wells 1234	1234
7	Wells Fargo 123	123
8	Wells Fargo 1234	1234
9	Wells Fargo Inc 123	123

You're welcome. Don't forget to thank those who helped by clicking on Add Reputation * and please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).

Change the formula in C2 to:

=MID(A2,MIN(FIND({1,2,3,4,5,6,7,8,9,0},A2&1234567890)),20)