Full text from the custom format of the cell

[theteacher9999]

Hello,

I'm struggling with the following issue.

In C2 I have this formula because I want to get the last word from A in C column (it shoould be 'Last' instead of '63636'):

=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",100)),100))

The problem is that A2 cell has a strange custom format, as shown in the picture below:

Is there any way to get a full text of A2? I know I can do it by copying it to Text Box and paste it in the same field but I will have more cells formatted this way. (Note that in this particular example A3 doesn't have any custom format and what I got it in C3 looks good).

Ultimately, I plan to create columns B and C with VBA but the problem is the strange format in column A.

 

[rlv01]

One way would be to do it in VBA with a UDF

'UDF
Function LastWord2(MyCell As Range) As String
    Dim S As String, SA
    
    Application.Volatile
    SA = Split(MyCell.Text, " ")
    LastWord2 = SA(UBound(SA))
End Function

tmp1.xlsm
	A	B	C	D
1	original	amount	lastword	lastword2
2	66777.00 GBP Last	66777	66777	Last
Sheet5

Cell Formulas
Range	Formula
B2	B2	=A2
C2	C2	=TRIM(RIGHT(SUBSTITUTE(A2," ",REPT(" ",100)),100))
D2	D2	=lastword2(A2)

 

[theteacher9999]

Thanks a million, rlv01!