Copy paste custom formating as text

[nagovind]

Dears,

I have a cell A1 with custom formatting as 0.0" RUNs"

if we type 10 in cell A1 then it appears as 10.0 RUNs in cell A1

Need to copy the cell A1 content and paste in say cell C1 as 10.0 RUNs as FULL TEXT

so the cell content of C1 shall be 10.0 RUNs not only 10

Please advise

Regards
nagovind

 

[NARAYANK991]

Hi ,

I doubt that a copy + paste will do what you want done ; either using a formula (=TEXT(A1, "0.0") & " RUNS"), or using the VBA statement Range("A1").Text will transfer the data as complete text.

In both cases , the data will cease to be a number , and will be converted to a text string.

Narayan

 

[vletm]

Change Your custom formatting from 0.0" RUNs" to 0,0"RUNs"
=>
traditional Ctrl+C and Ctrl+V works!

 

[nagovind]

Dear Narayan,

Thank you for your reply. The actual requirement is for generalised requirement to extract the actual from a cell with custom formated cell

Kindly advise

 

[nagovind]

Dear Narayan,

VBA code worked for me
Got the generalised solution

Thank you very much

 

[bosco_yip]

Another option in formula way

1] Your data in A1:A3

2] In C1 >> Define Name

Name : Format

Refers to : =GET.CELL(7,Sheet1!$A1)

And,

3] In C1 enter formula and copy down :

=TEXT(A1,Format)

Regards
Bosco

p.s. since Gell.Cell is a Macro 4 function, file will be saved in MS Excel Macro-Enable Worksheet

 

[nagovind]

@Bosco, thanks a lot yeah it is working got it....