Copy only basic number without dollar sign or commas (or cell formatting)?

[StargateFanFromWork]

Would there be a way to have XL2K allow user to copy a cell's result but
paste it without dollar sign and comma?

What I mean is that cell K2 shows result of, say, $5,496.64 with formula of
=SUM(J3:J65506)

Is there a way to get it to make paste show up as the basic number, also
without any of the cell formatting, so that result is

5496.64

instead of $5,496.64?


Thanks! D

 

[JNW]

Copy then pastespecial with just values.

 

[Tom Ogilvy]

Edit=>Paste special and select values.

 

[StargateFanFromWork]

Edit=>Paste special and select values.

I'm sorry. I wasn't clear. I need a vb solution which is why I posted to
this ng <g>, and the app I'm copying into doesn't have paste special. Sorry
'bout that.

So I'd need the vb code to hopefully copy and remove the $ and , from the
value so that when I paste to another application, I get just the basic
number.
i.e., in my example below, I'd take the result of the formula
=SUM(J3:J65506) of, say,

$15,278.34

and get a paste of

15278.34

The application I'm pasting into is a custom-built one where I work and it
doesn't accept the $ sign and comma in numbers like $15,278.34. It would
save me time to have something like this, so here's hoping! <g>

Thanks! D

 

[StargateFanFromWork]

Does the lack of answer mean this is not possible?

Cheers. D

 

[StargateFanFromWork]

Does the lack of answer mean this is not possible?

Cheers. D

Again, thanks for the responses so far, but I'm working out of XL2K and into
another application. The amount in the formula comes from XL2K but I need
it to copy the amount without the $ and , so that when I go into the other
application, I get the desired results.

Perhaps behind-the-scenes, XL2K can copy the cell and paste it virtually
somewhere, strip the $ and , and then copy that to the clipboard?? Then I
could just paste straight from the clipboard in the desired format. Again,
I haven't a clue how to do this, but perhaps someone here might know a
possible workaround??

Any suggestions welcome on how to do this.

Thanks. D

 

[JE McGimpsey]

One possible solution:

Public Sub CopyUnformatted()
Dim vArr As Variant
Dim i As Long
Application.ScreenUpdating = False
With Selection
ReDim vArr(1 To .Count)
For i = 1 To .Count
vArr(i) = .Cells(i).NumberFormat
Next i
.NumberFormat = "General"
.Copy
For i = 1 To .Count
.Cells(i).NumberFormat = vArr(i)
Next i
End With
Application.ScreenUpdating = True
End Sub

 

[StargateFanFromWork]

One possible solution:

Public Sub CopyUnformatted()
Dim vArr As Variant
Dim i As Long
Application.ScreenUpdating = False
With Selection
ReDim vArr(1 To .Count)
For i = 1 To .Count
vArr(i) = .Cells(i).NumberFormat
Next i
.NumberFormat = "General"
.Copy
For i = 1 To .Count
.Cells(i).NumberFormat = vArr(i)
Next i
End With
Application.ScreenUpdating = True
End Sub

Hi, thanks!

What does this copy from, pls? Since I didn't see a reference, I assumged
it would work with the selected cell.

When I used H15 as a test, which contains the value $26,549.46, I get a zero
value in both G16 and H16 (formatted as 0 and $0.00, respectively due to
formatting) and did not end up with 26549.46. The fact that the 2 cells get
zero in them is weird as they're not linked via a formula in any way. Also,
after the values get dumped into the cells, there is nothing left in the
clipboard to go to the other program to paste the info from Excel so that
part is missing. so the $26,549.46 disappears with this macro <g>.

What am I doing wrong, pls?

Cheers. D