Excel: how to concatenate a number and a string

Question

I have to write a position in the cell in the form of "1." with a macro. Whatever way I have tried to concatenate a number and a dot, it always only writes a number. If I add a strin before the number, the output is written properly. If I add another character after the dot, it also writes the outcome properly. Examples:

Sub concat()
Dim currentSht As Worksheet
Dim position, dot As String
Dim checkRow1 As Integer
Set currentSht = Sheets("Predtekmovanje")
position = "2"
dot = ".h"
currentSht.Range("AY8").Value = CStr(position) & dot
End Sub

If somebody finds a clever way on how to make the output in the form of 2. in the cell, I would really appresciate it.

Are you sure that the actual data of the cell is wrong? It sounds like the issue is that Excel formats the value as a number. — Lars Kristensen
– Lars Kristensen, Commented Aug 23, 2016 at 11:55

cyboashu · Accepted Answer · 2016-08-23 11:55:59Z

1

First change the number format to Text and then add the value.

currentSht.Range("AY8").NumberFormat="@"
currentSht.Range("AY8").Value="1."

answered Aug 23, 2016 at 11:55

cyboashu

10.5k2 gold badges32 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Ambie · Accepted Answer · 2016-08-23 11:59:59Z

0

Have you tried a custom number format? Like this:

With Sheet1.Range("A2")
    .NumberFormat = "#."
    .Value = position
End With

answered Aug 23, 2016 at 11:59

Ambie

4,9872 gold badges16 silver badges30 bronze badges

Collectives™ on Stack Overflow

Excel: how to concatenate a number and a string

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related