Add character to string in a cell

[brent_milne]

I have column B with varying amounts of rows that I need to add a character to the beginning of the string and another character to the string 3 places in the string. Hoping for some vba code loop to accomplish this.

eg. 12345678 would become x12x345678

have tried various things but nothing comes out the way I was hoping.
Thanks for any help

[amotto11]

you don't have to use VBA for this, you can use this formula

Please Login or Register  to view this content.

[JBeaucaire]

1) In an adjacent empty column, enter this formula,
="x" & LEFT(B2, 2) & "x" & MID(B2, 3, LEN(B2))


2) Copy down the data set

3) Copy the new column and PAST SPECIAL > VALUES over the original column B.

[TMS]

Please Login or Register  to view this content.

Regards, TMS

[brent_milne]

I will need VBA as it will be part of a larger macro that is formatting a set of numbers that has been pasted into that column.

[amotto11]

If that is the case, then TMS's solution should work. In the future, if you are unsure of how to create a macro, then just record yourself doing the action and you will have it saved. This is a big help to learning VBA.

[JBeaucaire]

For a VBA solution, worksheet formulas are often faster than line by line editing in a loop, I would recommend the original solution, but in VBA form:

Please Login or Register  to view this content.

[TMS]

Please Login or Register  to view this content.

[Keebellah]

If it's a fixed format you can just use the numberformat

Select any cell
Enter 1234567890

run the macro below

Please Login or Register  to view this content.

The result: 00x001x2345x7890

You can use any character except d m y becasue then it's translated to day month or year

And of course you can do it by simply selecting the cell or cells and changing the format, select Number and then custom end type the 00x000x00000x0000