Copy values + extra letters (custom formatting) => paste all as a value

[kirkkain]

Hello!

I have a problem in copying data from custom formatted cell. I have multiple cells that include numeric values. After these values there are added letters such as a, b or ab. The example cell would look like (11,46 ab). However the ab is a result of custom formatting, and due to that, when i copy cell contents i can not paste the whole string. I can paste only the value (11,46) or the value and the formatting.

I need to paste the contents of these cells as plain text without formatting, so i could use VBA to search for the letters (a, b or ab) and superscript them. As far as i know VBA cannot search for text in custom formatting and superscript certain parts of it.

Thank you for reading!

 

[FormR]

Hi, couple of thoughts.

1. Maybe you can copy the custom formatted cells one by one to a new range using the .Text property of the cell.
2. Maybe you can copy the unformatted values to an array and then apply the same logic as the CF to append the "added letters" to each element in the array before writing them back to the sheet.

 

[kirkkain]

Hey!
Thanks for you answer, I'm having a really hard time understading how the TEXT-formula works, even with the help of google search results. I can manage to extract the variable numberic values but have no luck in extacting the letter. Do you know a potential formatting i need to use with TEXT-formula to get the desired output? The original cells include data with variable values and letters such as; "2,083 a" or "4,543 b".

Thank you for your time!

 

[FormR]

I'm having a really hard time understading how the TEXT-formula works

Hi, I was suggesting that you use VBA and refer to the text property of the range object - not use the TEXT() formula.

How many different conditional formatting rules do you have and what are they?
What range of cells (and what sheet) contain the condtionally formatted values?
Where do you want them copying to?

 

[kirkkain]

Ahh okay, so i have 5 different conditional formatting rules, which are by the way created by excel add-in "XLstat". Rules are: (0,000" a") (0,000" ab") (0,000" b") (0,000" bc") and (0,000" c"). Range of cells is going to vary between projects so the range should be okay as a "selection". The sheet is called "ANOVA" and I am pasting (and transposing) the range to a sheet called "Homogenous subsets" starting from A1.

Thank you for your help!

 

[FormR]

i have 5 different conditional formatting rules, which are by the way created by excel add-in "XLstat" Rules are: (0,000" a") (0,000" ab") (0,000" b") (0,000" bc") and (0,000" c").

I'm not aware of that Add-In, but I was more interested in the logic that determines which rule is applied - I was thinking that we could maybe replicate the logic using formulas. Might not be possible or easy but thought it was worth investigating.

Range of cells is going to vary between projects so the range should be okay as a "selection". The sheet is called "ANOVA" and I am pasting (and transposing) the range to a sheet called "Homogenous subsets" starting from A1.

You could try code like this.

Sub CopyFormattedCells()
Dim c As Range, i As Long
Application.ScreenUpdating = False
For Each c In Selection
  Sheets("ANOVA").Range("A1").Offset(, i).Value = c.Text
  i = i + 1
Next c
Application.ScreenUpdating = True
End Sub

 

[kirkkain]

I'm not aware of that Add-In, but I was more interested in the logic that determines which rule is applied - I was thinking that we could maybe replicate the logic using formulas. Might not be possible or easy but thought it was worth investigating.



You could try code like this.

Sub CopyFormattedCells()
Dim c As Range, i As Long
Application.ScreenUpdating = False
For Each c In Selection
  Sheets("ANOVA").Range("A1").Offset(, i).Value = c.Text
  i = i + 1
Next c
Application.ScreenUpdating = True
End Sub

Hey! Thank you for help!

The logic behind the formatting comes form statistical analysis and is applied to the values if they are from groups that are not statistically different.

The code you pasted actually copied the full content of cells and pasted it as plain text which is definatley what I am looking for. However, it pasted all the values in a single row. My data is in a table and it would be nice if the selection could be pasted "as is" or transposed which i can add to the VBA even on my own.

 

[FormR]

The logic behind the formatting comes form statistical analysis and is applied to the values if they are from groups that are not statistically different.

Understood - sounds like (an easy) formula approach is probably not feasible.

The code you pasted actually copied the full content of cells and pasted it as plain text which is definatley what I am looking for.

Great, good to hear

which i can add to the VBA even on my own.

Yes - it was just an illustration really - but feel free to post back with the details if you get stuck modifying it for your specific set-up.

 

[kirkkain]

Yes - it was just an illustration really - but feel free to post back with the details if you get stuck modifying it for your specific set-up.

Actually, i do have a problem to modify your code that i would copy and paste a whole selected array. At this moment, you code copies the selected array, but pastes it to one continuous row of cells.

Do you think your code can be modified to copy and paste a whole intact array/table?

Thanks for your time!

 

[FormR]

Do you think your code can be modified to copy and paste a whole intact array/table?

Something like this maybe.:

Sub CopyFormattedCells()
Dim i As Long, j As Long
Application.ScreenUpdating = False
For i = 1 To Selection.Rows.Count
  For j = 1 To Selection.Columns.Count
    Sheets("ANOVA").Range("A1").Offset(j - 1, i - 1).Value = Selection.Cells(i, j).Text
  Next j
Next i
Application.ScreenUpdating = True
End Sub