MrExcel Publishing
Your One Stop for Excel Tips & Solutions

How can I unstring a Cell using a MACRO

Posted by Billy on July 20, 2001 9:23 PM

I have a exported worksheet from Microsoft project. The cell will look like -


I want to extract the nn. The name part is of a variable length.

How can I extract the nn part? I am using macros, but there is no UNSTRING function.

Posted by Malc on July 21, 2001 12:26 AM

Only way out is to use the string functions Left Right Mid & Len. Try using the paste function wizard and record a macro to see how it does it and then get the macro to put the formula in the spreadsheet. If the formula is to long the recorder won't record it all but if you change the col and rows to R1C1 in the options memo and copy the formula from the formula bar it can be pasted into a macro.

Anyway on the spreadsheet it'll be something like
=(mid(A1,len(A1)-6,len(A1)-1) Assume the first six characters are constant.
If you don't want to paste the function onto the spreadsheet experiment with the same functions in macro to see what you get, the formula won't be that much different in code.