Concatenate variable with leading zeros with another string

Scenario

I have an excel cell that contains value with leading zeros (Eg: 0002). My macro is copying this value to a variable called runNumber and pasting it into another file by concatenating this runNumber with some other string. But when it does that, I am missing the leading zeros of runNumber

Codes

yearInYy = "19"

ciNumber = "PFTPA-" & yearInYy & "-" & runNumber

Output cell showing as PFTPA-19-2

What I need

I need the output cell to show

PFTPA-19-0002

Anyone knows how to do it?

edited Mar 7 at 0:52

asked Jan 2 at 9:21

Anu

175115

add a comment |

Scenario

Codes

yearInYy = "19"

ciNumber = "PFTPA-" & yearInYy & "-" & runNumber

Output cell showing as PFTPA-19-2

What I need

I need the output cell to show

PFTPA-19-0002

Anyone knows how to do it?

edited Mar 7 at 0:52

asked Jan 2 at 9:21

Anu

175115

add a comment |

Scenario

Codes

yearInYy = "19"

ciNumber = "PFTPA-" & yearInYy & "-" & runNumber

Output cell showing as PFTPA-19-2

What I need

I need the output cell to show

PFTPA-19-0002

Anyone knows how to do it?

edited Mar 7 at 0:52

asked Jan 2 at 9:21

Anu

175115

Scenario

Codes

yearInYy = "19"

ciNumber = "PFTPA-" & yearInYy & "-" & runNumber

Output cell showing as PFTPA-19-2

What I need

I need the output cell to show

PFTPA-19-0002

Anyone knows how to do it?

excel vba string-concatenation

edited Mar 7 at 0:52

asked Jan 2 at 9:21

Anu

175115

edited Mar 7 at 0:52

asked Jan 2 at 9:21

Anu

175115

edited Mar 7 at 0:52

asked Jan 2 at 9:21

Anu

175115

asked Jan 2 at 9:21

Anu

175115

asked Jan 2 at 9:21

Anu

175115

add a comment |

3 Answers
3

active

oldest

votes

ciNumber = "PFTPA-" & yearInYy & "-" & Format(runNumber, "0000")

Using the Format function, you can convert a date or number to a string with a specific format. All we do here is require 4 digits, with leading zeros.

The equivalent in Excel itself is the TEXT function

answered Jan 2 at 10:05

Chronocidal

3,0211317

add a comment |

In order to add leading zeroes to a string, you don't need VBA, you can use following Excel formula for this:

=CONCATENATE(REPT("0";4-LEN(D4));D4)

It works as follows:

Take the length of your string, and subtract this from 4 (it will give you the number of "0" characters you need).

Create a string, which consists of a repetition of "0" characters.

Concatenate that repetition of strings to your original string.

answered Jan 2 at 9:44

Dominique

2,11241941

add a comment |

Assuming the field containing the 0002 is formatted as a text field then work with strings throughout. Here I am reading runNumber from cell A1. This means you can deal with a variable number of leading zeroes.

Option Explicit

Public Sub test()

    Dim runNumber As String, yearInYy As String, ciNumber As String

    yearInYy = "19"

    runNumber = [A1]

    ciNumber = "PFTPA-" & yearInYy & "-" & runNumber

    Debug.Print ciNumber

End Sub

edited Jan 2 at 9:58

answered Jan 2 at 9:27

QHarr

34.8k82044

Issue with your method is, I need to pre format cells of the source as text which I don't wish to. Answer from another person above will not format cells of source text and instead, it format the variable into 0000 form. I prefer to do that way. Thanks for your effort. By the way just curious what is that [A1] means? Does it mean Range("A1").value?

– Anu
Jan 3 at 0:27

1

I was trying to cater for the fact you can have variable number of zeroes and there is no statement that that number will always be the same length. Also, if that cell is not text formatted how do you have the 000 at the front as per your description? it’s an alternative notation for range so range A1.

– QHarr
Jan 3 at 6:19

It is still possible without formatting the cell. I am able to do it with the help from another person here in stackoverflow. it is working by following this code ciNumber = "PFTPA-" & yearInYy & "-" & Format(runNumber, "0000")

– Anu
Jan 3 at 9:33

1

What I am saying is that you said you have 0002 in the cell. How are the zeroes present if the cell is not formatted? If you had just 2 in the cell and wanted 0002 that would be different. You can also use Format$ rather than Format for minor efficiency gains.

– QHarr
Jan 3 at 12:50

1

That’s alright :-) note my point about typed function Format$

– QHarr
Jan 4 at 0:10

|
show 3 more comments

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54003845%2fconcatenate-variable-with-leading-zeros-with-another-string%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

3 Answers
3

active

oldest

votes

3 Answers
3

active

oldest

votes

ciNumber = "PFTPA-" & yearInYy & "-" & Format(runNumber, "0000")

Using the Format function, you can convert a date or number to a string with a specific format. All we do here is require 4 digits, with leading zeros.

The equivalent in Excel itself is the TEXT function

answered Jan 2 at 10:05

Chronocidal

3,0211317

add a comment |

ciNumber = "PFTPA-" & yearInYy & "-" & Format(runNumber, "0000")

Using the Format function, you can convert a date or number to a string with a specific format. All we do here is require 4 digits, with leading zeros.

The equivalent in Excel itself is the TEXT function

answered Jan 2 at 10:05

Chronocidal

3,0211317

add a comment |

ciNumber = "PFTPA-" & yearInYy & "-" & Format(runNumber, "0000")

Using the Format function, you can convert a date or number to a string with a specific format. All we do here is require 4 digits, with leading zeros.

The equivalent in Excel itself is the TEXT function

answered Jan 2 at 10:05

Chronocidal

3,0211317

ciNumber = "PFTPA-" & yearInYy & "-" & Format(runNumber, "0000")

Using the Format function, you can convert a date or number to a string with a specific format. All we do here is require 4 digits, with leading zeros.

The equivalent in Excel itself is the TEXT function

answered Jan 2 at 10:05

Chronocidal

3,0211317

answered Jan 2 at 10:05

Chronocidal

3,0211317

answered Jan 2 at 10:05

Chronocidal

3,0211317

answered Jan 2 at 10:05

Chronocidal

3,0211317

add a comment |

In order to add leading zeroes to a string, you don't need VBA, you can use following Excel formula for this:

=CONCATENATE(REPT("0";4-LEN(D4));D4)

It works as follows:

Take the length of your string, and subtract this from 4 (it will give you the number of "0" characters you need).

Create a string, which consists of a repetition of "0" characters.

Concatenate that repetition of strings to your original string.

answered Jan 2 at 9:44

Dominique

2,11241941

add a comment |

In order to add leading zeroes to a string, you don't need VBA, you can use following Excel formula for this:

=CONCATENATE(REPT("0";4-LEN(D4));D4)

It works as follows:

Take the length of your string, and subtract this from 4 (it will give you the number of "0" characters you need).

Create a string, which consists of a repetition of "0" characters.

Concatenate that repetition of strings to your original string.

answered Jan 2 at 9:44

Dominique

2,11241941

add a comment |

In order to add leading zeroes to a string, you don't need VBA, you can use following Excel formula for this:

=CONCATENATE(REPT("0";4-LEN(D4));D4)

It works as follows:

Take the length of your string, and subtract this from 4 (it will give you the number of "0" characters you need).

Create a string, which consists of a repetition of "0" characters.

Concatenate that repetition of strings to your original string.

answered Jan 2 at 9:44

Dominique

2,11241941

In order to add leading zeroes to a string, you don't need VBA, you can use following Excel formula for this:

=CONCATENATE(REPT("0";4-LEN(D4));D4)

It works as follows:

Take the length of your string, and subtract this from 4 (it will give you the number of "0" characters you need).

Create a string, which consists of a repetition of "0" characters.

Concatenate that repetition of strings to your original string.

answered Jan 2 at 9:44

Dominique

2,11241941

answered Jan 2 at 9:44

Dominique

2,11241941

answered Jan 2 at 9:44

Dominique

2,11241941

answered Jan 2 at 9:44

Dominique

2,11241941

add a comment |

Option Explicit

Public Sub test()

    Dim runNumber As String, yearInYy As String, ciNumber As String

    yearInYy = "19"

    runNumber = [A1]

    ciNumber = "PFTPA-" & yearInYy & "-" & runNumber

    Debug.Print ciNumber

End Sub

edited Jan 2 at 9:58

answered Jan 2 at 9:27

QHarr

34.8k82044

Issue with your method is, I need to pre format cells of the source as text which I don't wish to. Answer from another person above will not format cells of source text and instead, it format the variable into 0000 form. I prefer to do that way. Thanks for your effort. By the way just curious what is that [A1] means? Does it mean Range("A1").value?

– Anu
Jan 3 at 0:27

1

I was trying to cater for the fact you can have variable number of zeroes and there is no statement that that number will always be the same length. Also, if that cell is not text formatted how do you have the 000 at the front as per your description? it’s an alternative notation for range so range A1.

– QHarr
Jan 3 at 6:19

It is still possible without formatting the cell. I am able to do it with the help from another person here in stackoverflow. it is working by following this code ciNumber = "PFTPA-" & yearInYy & "-" & Format(runNumber, "0000")

– Anu
Jan 3 at 9:33

1

What I am saying is that you said you have 0002 in the cell. How are the zeroes present if the cell is not formatted? If you had just 2 in the cell and wanted 0002 that would be different. You can also use Format$ rather than Format for minor efficiency gains.

– QHarr
Jan 3 at 12:50

1

That’s alright :-) note my point about typed function Format$

– QHarr
Jan 4 at 0:10

|
show 3 more comments

Option Explicit

Public Sub test()

    Dim runNumber As String, yearInYy As String, ciNumber As String

    yearInYy = "19"

    runNumber = [A1]

    ciNumber = "PFTPA-" & yearInYy & "-" & runNumber

    Debug.Print ciNumber

End Sub

edited Jan 2 at 9:58

answered Jan 2 at 9:27

QHarr

34.8k82044

Issue with your method is, I need to pre format cells of the source as text which I don't wish to. Answer from another person above will not format cells of source text and instead, it format the variable into 0000 form. I prefer to do that way. Thanks for your effort. By the way just curious what is that [A1] means? Does it mean Range("A1").value?

– Anu
Jan 3 at 0:27

1

I was trying to cater for the fact you can have variable number of zeroes and there is no statement that that number will always be the same length. Also, if that cell is not text formatted how do you have the 000 at the front as per your description? it’s an alternative notation for range so range A1.

– QHarr
Jan 3 at 6:19

It is still possible without formatting the cell. I am able to do it with the help from another person here in stackoverflow. it is working by following this code ciNumber = "PFTPA-" & yearInYy & "-" & Format(runNumber, "0000")

– Anu
Jan 3 at 9:33

1

What I am saying is that you said you have 0002 in the cell. How are the zeroes present if the cell is not formatted? If you had just 2 in the cell and wanted 0002 that would be different. You can also use Format$ rather than Format for minor efficiency gains.

– QHarr
Jan 3 at 12:50

1

That’s alright :-) note my point about typed function Format$

– QHarr
Jan 4 at 0:10

|
show 3 more comments

Option Explicit

Public Sub test()

    Dim runNumber As String, yearInYy As String, ciNumber As String

    yearInYy = "19"

    runNumber = [A1]

    ciNumber = "PFTPA-" & yearInYy & "-" & runNumber

    Debug.Print ciNumber

End Sub

edited Jan 2 at 9:58

answered Jan 2 at 9:27

QHarr

34.8k82044

Option Explicit

Public Sub test()

    Dim runNumber As String, yearInYy As String, ciNumber As String

    yearInYy = "19"

    runNumber = [A1]

    ciNumber = "PFTPA-" & yearInYy & "-" & runNumber

    Debug.Print ciNumber

End Sub

edited Jan 2 at 9:58

answered Jan 2 at 9:27

QHarr

34.8k82044

edited Jan 2 at 9:58

answered Jan 2 at 9:27

QHarr

34.8k82044

answered Jan 2 at 9:27

QHarr

34.8k82044

answered Jan 2 at 9:27

QHarr

34.8k82044

Issue with your method is, I need to pre format cells of the source as text which I don't wish to. Answer from another person above will not format cells of source text and instead, it format the variable into 0000 form. I prefer to do that way. Thanks for your effort. By the way just curious what is that [A1] means? Does it mean Range("A1").value?

– Anu
Jan 3 at 0:27

1

I was trying to cater for the fact you can have variable number of zeroes and there is no statement that that number will always be the same length. Also, if that cell is not text formatted how do you have the 000 at the front as per your description? it’s an alternative notation for range so range A1.

– QHarr
Jan 3 at 6:19

It is still possible without formatting the cell. I am able to do it with the help from another person here in stackoverflow. it is working by following this code ciNumber = "PFTPA-" & yearInYy & "-" & Format(runNumber, "0000")

– Anu
Jan 3 at 9:33

1

What I am saying is that you said you have 0002 in the cell. How are the zeroes present if the cell is not formatted? If you had just 2 in the cell and wanted 0002 that would be different. You can also use Format$ rather than Format for minor efficiency gains.

– QHarr
Jan 3 at 12:50

1

That’s alright :-) note my point about typed function Format$

– QHarr
Jan 4 at 0:10

|
show 3 more comments

Issue with your method is, I need to pre format cells of the source as text which I don't wish to. Answer from another person above will not format cells of source text and instead, it format the variable into 0000 form. I prefer to do that way. Thanks for your effort. By the way just curious what is that [A1] means? Does it mean Range("A1").value?

– Anu
Jan 3 at 0:27

1

I was trying to cater for the fact you can have variable number of zeroes and there is no statement that that number will always be the same length. Also, if that cell is not text formatted how do you have the 000 at the front as per your description? it’s an alternative notation for range so range A1.

– QHarr
Jan 3 at 6:19

It is still possible without formatting the cell. I am able to do it with the help from another person here in stackoverflow. it is working by following this code ciNumber = "PFTPA-" & yearInYy & "-" & Format(runNumber, "0000")

– Anu
Jan 3 at 9:33

1

What I am saying is that you said you have 0002 in the cell. How are the zeroes present if the cell is not formatted? If you had just 2 in the cell and wanted 0002 that would be different. You can also use Format$ rather than Format for minor efficiency gains.

– QHarr
Jan 3 at 12:50

1

That’s alright :-) note my point about typed function Format$

– QHarr
Jan 4 at 0:10

Issue with your method is, I need to pre format cells of the source as text which I don't wish to. Answer from another person above will not format cells of source text and instead, it format the variable into 0000 form. I prefer to do that way. Thanks for your effort. By the way just curious what is that [A1] means? Does it mean Range("A1").value?

– Anu
Jan 3 at 0:27

I was trying to cater for the fact you can have variable number of zeroes and there is no statement that that number will always be the same length. Also, if that cell is not text formatted how do you have the 000 at the front as per your description? it’s an alternative notation for range so range A1.

– QHarr
Jan 3 at 6:19

It is still possible without formatting the cell. I am able to do it with the help from another person here in stackoverflow. it is working by following this code ciNumber = "PFTPA-" & yearInYy & "-" & Format(runNumber, "0000")

– Anu
Jan 3 at 9:33

What I am saying is that you said you have 0002 in the cell. How are the zeroes present if the cell is not formatted? If you had just 2 in the cell and wanted 0002 that would be different. You can also use Format$ rather than Format for minor efficiency gains.

– QHarr
Jan 3 at 12:50

That’s alright :-) note my point about typed function Format$

– QHarr
Jan 4 at 0:10

|
show 3 more comments

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Bdtjtk