Assume that you are having the below value in A1 cell.
A1
Group & Organization Management 1059-6011
Method 1:-
If the Numbers are appearing in the end of the cell content (i.e.) last word
of that cell, then use the below method
Paste this formula in C1 cell
=RIGHT(A1,LEN(A1)-FIND("*",SUBSTITUTE(A1," ","*",LEN(A1)-LEN(SUBSTITUTE(A1,"
","")))))
Paste this formula in B1 cell
=TRIM(SUBSTITUTE(A1,C1,""))
This will give you the result once the C1 cell is filled with the above
formula.
Method 2:-
If the position of the Number is unpredictable, then use the below method
Paste this formula in C1 cel
=MID(A1,MATCH(0,(ISERROR(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*1)*1),0),LEN(A1)-SUM((ISERROR(MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)*1))))
This is an array formula. So copy the above formula and place the cursor in
C1 cell and press F2 and give Cntrl+V and instead of enter Press
Cntrl+Shift+Enter.
Paste this formula in B1 cell
=TRIM(SUBSTITUTE(A1,C1,""))
This will give you the result once the C1 cell is filled with the above
array formula.
Remember to Click Yes, if this post helps!