Concatenate text string as values

[Ravana]

Hello all.

I have this text string

=" The IOCPF Advice No. FIN/CPF/"&MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)&"/"&MATCH(UPPER(TEXT((CONTROL!$C$18),"mmmm")),{"MARCH","APRIL","MAY","JUNE","JULY","AUGUST","SEPTEMBER","OCTOBER","NOVEMBER","DECEMBER","JANUARY","FEBRUARY"},0)&"/"&CONTROL!$C$13&" dated: "&TEXT(CONTROL!$C$29,"dd-mmm-yyyy")&" for Rs. "&TEXT(CONTROL!$C$274,"#,##0")&"/- ("&SpellIndian(CONTROL!$C$274)&") for the month of "&UPPER(TEXT(CONTROL!$C$18,"mmmm - YYYY"))&" in respect of Cat I employees working under control of this office along with the following enclosures is sent herewith in duplicate for necessary action at your end."

from A1 to I1 (A to I are merged).

When I tried ".value = .value" or ".PasteSpecial Paste:=xlValues", I am getting #VALUE! error and for some other cells #name error.

Help how to output these cells as also values.

[:) Sixthsense :)]

Attach a sample workbook. Make sure there is just enough data to make it clear what is needed. Include a BEFORE sheet and an AFTER sheet in the workbook if needed to show the process you're trying to complete or automate. Make sure your desired results are demonstrated, mock them up manually if needed. Remember to desensitize the data.

Click on GO ADVANCED and use the paperclip icon to open the upload window.

View Pic

[Ravana]

Hi

I am working on a sheet which have multiple links to another sheet within workbook (which contains certain sensitive data- which might be against the forum rules, which says data should be desensitized). I cannot desensitize hence, I am unable to upload any worksheet.

Issue, I am having is all formula cells are being copied as values except those cells which have both formula and text combined (eg: SUM(A1:A4)&"persons"). These cells are returned as errors.

So help plz.