How to identify if a Cell has has more than text letters?

[henry fam]

Hi All:
I have certain info in one column as below. I'd like to use a formula that would show me only the 4 last numbers if it has the format CC - xxxx (see in bold below). For those that don't have the same format (in red), they should remain the same.
(What I did was: For the first 3 rows, I could use =Right(A1,4) to come up with 4 last number; however, for those in red, it would show: /21), U 20, /13, PMTS, RRIS, etc, which I don't want them that way. I just want those to remain unchanged.
Please help. Thanks so much.
Henry
[TABLE="width: 123"]
<tbody>[TR]
[TD]CC - 4469[/TD]
[/TR]
[TR]
[TD]CC - 9762[/TD]
[/TR]
[TR]
[TD]CC - 0817
CC - 7679(9/21)[/TD]
[/TR]
[TR]
[TD]CC-TENN SU 20[TABLE="width: 123"]
<tbody>[TR]
[TD][TABLE="width: 123"]
<tbody>[TR]
[TD]CC-PHILA(9/13)[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[TABLE="width: 123"]
<tbody>[TR]
[TD]CC-0061 (9/25)
CC-10/1PMTS[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[TABLE="width: 123"]
<tbody>[TR]
[TD]CC-10/8 FERRIS[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]
[/TD]
[/TR]
</tbody><colgroup><col></colgroup>[/TABLE]

 

[henry fam]

Thanks so much PGC01 and Istvan. I'd very much appreciate your help. It has definitely saved a lot of my time.
You all have a nice weekend.

 

[Rick Rothstein]

Hi,

Perhaps:

=IF(AND(MID(TRIM(A1),LEN(A1)-5,1)="-",SUMPRODUCT(--(ISNUMBER(--(RIGHT(TRIM(A1),ROW(INDIRECT("1:4")))))))=4),RIGHT(A1,4),A1)

Regards

If you think you need the TRIM's that I highlighted above, then I think you need the one I added (and highlighted) below...

=IF(AND(MID(TRIM(A1),LEN(A1)-5,1)="-",SUMPRODUCT(--(ISNUMBER(--(RIGHT(TRIM(A1),ROW(INDIRECT("1:4")))))))=4),RIGHT(TRIM(A1),4),A1)

Also, since the count is fixed at 4, the part I highlighted in green can be replaced with this...{1,2,3,4}

=IF(AND(MID(TRIM(A1),LEN(A1)-5,1)="-",SUMPRODUCT(--(ISNUMBER(--(RIGHT(TRIM(A1),{1,2,3,4})))))=4),RIGHT(TRIM(A1),4),A1)

If you are willing to accept that the OP's sample data is fully representative, and that the TRIM's can be eliminated because the extra space characters will not be a problem, you can simplify the formula to this...

=IF(ISNUMBER(-RIGHT(A1,4)),RIGHT(A1,4),A1)

 

[XOR LX]

Completely agreed.

In fact, the formula I posted was not thought-through at all, for which I should apologise (for one, I intended the RIGHT to be a MID, which would overcome the issue PGC pointed out - using RIGHT like that, as you point out, is redundant.) Not to mention the oversight with TRIM.

Guess I was just having one of those days...

Thanks a lot