Enforce string's format to: 65545,4324,65756 and not: 9.61174259958739E+141 [VBA]

[pitaszek]

I need to have a list of IDs in following format:
56546,933655,7546,23435,54,4454

but if I put it this way it's changed to mathematical notation, eg:
9.61174259958739E+141

I need to keep the format with no spaces after each comma and without a need to add '' ' " before it to enforce string.

My macro generally takes the list of numbers from a column and puts it horizontally with commas.

Any ideas?

 

[baitmaster]

I don't get this, so I'm unsure what you mean

When I try to paste that string into Excel, it stays as it is. My cells are formatted as "general".
Likewise if I use VBA [selection.value = "56546,933655,7546,23435,54,4454"], it also pastes as text

Suggestions:
- format your Excel range as text prior to writing the value in
- paste your code here so I can understand the issue better

 

[pitaszek]

Hello baitmaster,

Please try this and tell me what is pasted:

Sub test()
Dim value As String
vale = "43545,5757,3422,46578,5653,3454"
ActiveCell.value = vale
End Sub

Do you have Amer or EU number format?

Cheers,
Witek

 

[baitmaster]

I'm in UK so using UK, formats. Different date formats to US (dd/mm/yyyy) but different numeric formats to EU (123,456.78 for 123k to 2 decimal places)

Strangely your code passed a number into Excel, but text when I substituted the value from your original post

I suggest the following fix:

With ActiveCell
    .NumberFormat = "@"
    .value = vale
End With

 

[pitaszek]

baitmaster - thanks a lot, so simple, fast and effective.