alexander21
New member
- Joined
- Jan 9, 2014
- Messages
- 5
- Reaction score
- 0
- Points
- 0
Hi,
I've had a bit of trouble formatting my nested IF statement. The statement is designed to take a band or artist name, then take the last word of the name and output it. If it contains a number it will output the entire name.
The issue I'm having is when the formula is applied to empty cells (it needs to be there for the client, who do not have much Excel knowledge) - it returns a zero which is fair enough and I can remove this via formatting but that does not allow me to sort the data properly because it detects the zero cells.
To counter this I'm trying to use ISBLANK to remove the zeroes properly but I can't seem to get it to format properly.
What I'm currently using:
What I'm trying to use:
Sample of A2,A3,A4:
Output B2,B3,B4:
Thanks!!
I've had a bit of trouble formatting my nested IF statement. The statement is designed to take a band or artist name, then take the last word of the name and output it. If it contains a number it will output the entire name.
The issue I'm having is when the formula is applied to empty cells (it needs to be there for the client, who do not have much Excel knowledge) - it returns a zero which is fair enough and I can remove this via formatting but that does not allow me to sort the data properly because it detects the zero cells.
To counter this I'm trying to use ISBLANK to remove the zeroes properly but I can't seem to get it to format properly.
What I'm currently using:
Code:
=IFERROR(IF(COUNT(FIND({0,1,2,3,4,5,6,7,8,9},A2))>0,A2,IF(NOT(ISERR(SEARCH(" ",A2))),RIGHT(A2,LEN(A2)-FIND("|",SUBSTITUTE(A2," ","|",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))),A2)),"")
What I'm trying to use:
Code:
=IFERROR(IF(COUNT(FIND({0,1,2,3,4,5,6,7,8,9},A2))>0,A2,IF(NOT(ISERR(SEARCH(" ",A2))),RIGHT(A2,LEN(A2)-FIND("|",SUBSTITUTE(A2," ","|",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))),A2,IF(ISBLANK(B2),""),"")
Sample of A2,A3,A4:
Code:
JESSE JACKSON
JACKSON 5
CHI-LITES
Output B2,B3,B4:
Code:
JACKSON
JACKSON 5
CHI-LITES
Thanks!!
Last edited: