Formula to get cell custom format value

[diazyovera]

I have a list of thousands of items, there's a column with the number and the units. The units are set in custom format. for example:

Mud logging 11 DAYS

Cement 250 EA

Rig 90 HOURS

...

Now I,m trying to get in one colum only the units (DAYS, EA, ...). But I can't find any formula that retrieves only the units. The units where set in custom format.


I tried using =CELL("format",N2654) but returns "F1" for everything.


Thanks for any help.

 

[oldchippy]

Hi,


If your data in in A1:A3 then try this


=RIGHT(A1,LEN(A1)-FIND("^^",SUBSTITUTE(A1," ","^^",LEN(A1)-LEN(SUBSTITUTE(A1," ",""))))) and copy down