• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Formula to get cell custom format value


New Member
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.

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