macro to manipulate text

[jemc2000]

I have a column which contains data like this: F540044000
I need help writing a macro which will remove the zero in the 4th place, put 38 in the beginning, and remove the last two zeroes.
So it will look like this: 38F540440
The sheet changes, so I will need to be able to run this over and over again.
Thank you.

 

[Rocky E]

On 2002-04-30 11:07, jemc2000 wrote:
I have a column which contains data like this: F540044000
I need help writing a macro which will remove the zero in the 4th place, put 38 in the beginning, and remove the last two zeroes.
So it will look like this: 38F540440
The sheet changes, so I will need to be able to run this over and over again.
Thank you.

Hi,
You can do this with a formula:
="38"&LEFT(A1,3)&LEFT(RIGHT(A1,6),4)
Will that work or how will you use a macro? I may be just a little confused )

 

[jemc2000]

I get emailed this spreadsheet weekly. So every week I need to basically be able to hit a control-key and have it perform this function on a column of numbers (these are job numbers, usually about 500 of them).

 

[NateO]

You could use a procedure like the following:

Sub chgIn()
Dim cl As Range
For Each cl In [a:a] 'Change letters to column of choice
If cl <> "" Then cl = "32" & Left([cl].Value, 3) & Mid([cl], 5, 4)
Next
End Sub

Hope this helps.

 

[jemc2000]

You two are my heroes. Thanks!