VBA: Add formula to a cell's string using VBA

[Lefemmenikita]

HI

I am exporting a 6 digit number from a system into Excel. When this is pasted into excel, this is reformatted as a number instead of being left as a text value.

Column A with the code shows what the reformatted number looks like. Column B shows what I want it to look like.

Book1
	A	B
1	Code	Output Wanted
2	6571	006571
3	909856	909856
4	2210	002210
5	98521	098521
Sheet1

Cell Formulas
Range	Formula
B2:B5	B2	=REPT(0,6-LEN(A2))&A2

This is ok when the range of the reformatted cells remains the same and/or I am able to use a helper column like column B to insert a formula.

However, the reformatted numbers are not always in the same place and I am not always able to insert another column to insert a formula.

What I was wanting was a macro which allows me to reformat only the selected active cells.

e.g. If I select cell A4, I want VBA to insert a formula similar to =REPT(0,6-LEN(activecell.value))&Activecell.value

I have seen this done with 'round' formulas though I don't know how to adapt this to my current requirements.

Thanks

 

[Lefemmenikita]

Thank you so much Peter_SSs! This worked perfectly

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.