How to Extract Text from a custom formatted cell

Please try

Please Login or Register  to view this content.

UDF
Use in cell like
Select C6:D6, enter =GetSplit(B6), confirm with Ctrl + Shift + Enter(Array formula entry)
Copy down

Please Login or Register  to view this content.

If I understand what you want to do (use a cell value as part of a custom number format code), Excel itself does not have a way to do this. You can write a simple VBA procedure (associated with a suitable worksheet event, if desired) that will (step 1) read the desired text from a cell and (step 2) build the custom number format code from that text and (step 3) assign that number format code to the desired cell. Something like

Please Login or Register  to view this content.

I would add that I used to do this kind of thing when I first started using spreadsheets, but I quickly moved away from this practice. I found that when what I saw in the cell was too different from what the actual cell value was, that it created more confusion for me. Now, I much prefer to have value and unit text in different cells, and avoid combining them into single cell text strings or add unit text via number formatting. You decide if you like this approach, but I found that I did not like this way of doing things.