Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: macro to manipulate text

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  2. #2
    Board Regular
    Join Date
    Feb 2002
    Location
    North Alabama, USA
    Posts
    105
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 )
    Hope this helps!

    Rocky

    <h6>"Be not the first by whom the New are try'd,
    Nor yet the last to lay the Old aside."
    Alexander Pope (1688-1744).</h6>

  3. #3
    New Member
    Join Date
    Apr 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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).

  4. #4
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You could use a procedure like the following:

    Code:
    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.

  5. #5
    New Member
    Join Date
    Apr 2002
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    You two are my heroes. Thanks!

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •