Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Need a rename macro for excel

Need a rename macro for excel

RE: Need a rename macro for excel

I've found the easiest way to create macros for many things is to record them - start recording, perform the steps you want the macro to do, then stop recording. Then, it's just a matter of adding/editing to make it repeat the steps.

RE: Need a rename macro for excel

How about a spreadsheet solution?

Added 3 columns in lt green
My results in lt blue

E3: =IF(ISBLANK(C3),"",IF(SUM(C2:C3)=1,65,IF(C3=E$2,E2+IF(OR(E2=72,E2=78),2,1),E2)))
F3: =IF(C3<>F$2,"",IF(AND(C3=F$2,C2=1),65,F2+IF(OR(F2=72,F2=78),2,1)))
G3: =$G$2&TEXT(D3,"000")&IF(ISNUMBER(E3),CHAR(E3),"")&IF(ISNUMBER(F3),CHAR(F3),"")

Workbook below.

Do you realize that you're limited to 24 parts inside an assembly or sub-assembly BOM with this method?


glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Need a rename macro for excel

This Excel workbook solution looks interesting but considering i would have approx. 1000 parts to generate names for this would be a difficult task for a person who has less knowledge on Excel formulas.

RE: Need a rename macro for excel

The answer is a macro that pastes the formulas into the sheet when the user doesn't know how to use "FILL DOWN" or "COPY" and "PASTE" to duplicated the formulas.

You could also pre-populate the sheet with 2000 lines of formulas and protect the whole thing except the input column.

RE: Need a rename macro for excel

Unless your specifications are incorrect or incomplete, this could easily be accomplished if your table were a Structured Table (Insert > Tables > Table). I will send and example. Right now I'm not at my work station.

Procedural Steps
1. DELETE all but the FIRST ROW of DATA in the table. A short macro can do that.
2. PASTE your new list into the Current Assembly Name column. This will automatically cause all the formulas to propagate into all the rows of pasted data.
3. CHANGE the assembly prefix string.
4. Naturally you'll want to publish the generated names (all of which were generated by a formula) so you can COPY the appropriate columns an then PASTE VALUES into another Sheet that you can publish to the world.

BTW, I would hardly ever pre-fill rows with formulas. There are heartaches associated with such.


glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Need a rename macro for excel

I did a few modifications.

1) Inserted a Structured Table
2) Moved the New Assembly Name prefix above the table as well as 2 other pieces of data

So here's the drill. The data to Copy/Paste is on the Data sheet or you can use your own 2 columns of data.

1) Copy the 3 columns of data for new part numbers
2) Paste that data into the FIRST data row of the table

VoilĂ  y'all!


glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Need a rename macro for excel

Quote (BhagyeshVashi)

1000 parts to generate names for this would be a difficult task for a person who has less knowledge on Excel formulas.

You need NO KNOWLEDGE OF EXCEL FORMULAS to use the workbook I uploaded above. PASTE in 10,000 names if you like. The results are nearly instantaneous!


glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Need a rename macro for excel

This just came across my feed in YouTube yesterday.
Introducing REGEX Excel Functions

Leila has LOTS of videos on how to use Excel and does an excellent job of explaining how to do all manner of stuff in Excel.

RE: Need a rename macro for excel

Thanks for that dbill74. I hadn't heard about that, although it looks like they are not yet available in my copy of Excel 365.

For those who prefer to read about new stuff:

And for those who don't have the new functions yet, you can do it in VBA:

Doug Jenkins
Interactive Design Services

RE: Need a rename macro for excel


Some people, when confronted with a problem, think "I know, I'll use regular expressions." Now they have two problems.

The quote is from Jamie Zawinski, a world class hacker who I admire greatly. If he's telling us not to use regular expressions, should we even bother? Maybe, if you live and die by soundbites. But there's a bit more to the story than that, as evidenced by Jeffrey Friedl's exhaustive research on the Zawinski quote.

Zawinski himself commented on it. Analyzing the full text of Jamie's posts in the original 1997 thread, we find the following:

Perl's nature encourages the use of regular expressions almost to the exclusion of all other techniques; they are far and away the most "obvious" (at least, to people who don't know any better) way to get from point A to point B.
The first quote is too glib to be taken seriously. But this, I completely agree with. Here's the point Jamie was trying to make: not that regular expressions are evil, per se, but that overuse of regular expressions is evil.
See https://blog.codinghorror.com/regular-expressions-...

Having used regex I can say the first line is often closest to the truth; the problem is that it is very easy to make rules using regex that will either miss cases you think it should catch or catch cases that you expect it would not. The problem being that to avoid such problems one has to understand the entire permutation space that a regular expression might cover. Sure, the easy stuff is easy - picking off the first word on a line by matching the start of the line, some characters and then either (and here's where trouble starts) a space, or a tab, or a comma, or a colon, or a semicolon, or a dash/hyphen, or the end of line. And I am sure there is some character that I have missed which, in large unrefined data sets, leads to really interesting results.

Is regex bad? Nope. Used it a lot within AWK to hack and slash text files from one layout to another. It's a great tool. But it's a tool that has so much flexibility it can be a challenge to understand how to get a rigid solution.
That said, the OP is looking for a way to encode the location of an item in an assembly structure in a way that links to the assembly the items are used in; basically one way to identify the leaves on a directed graph by way of the entire path to that item. The top assembly is the primary node, each item on the list gets that node name and the arbitrary order under the node. Each assembly becomes a new node.

The original post is confusing; there is no sub-assembly1; there are items within sub-assembly1.

It would have been nice for the OP to write rules in a text specification for how the levels are created; like why do subassemblies only get a single letter when the top assemblies get 2? What happens if there are more than 26 items in a subassembly? Which letters are allowed? Is it as many as 26? Why is there a gap in Sr.No?

RE: Need a rename macro for excel

Here's my solution using normal functions. No macro required.
My approach is to first divide it into bite size pieces, so I deal with each level separately and combine those into the final result. It's easier to debug this way. I have used two columns to find the text for Level1. You can go back afterwards and combine these two columns if you want.
I have used a table. The advantage is that formulas will be auto filled down a column. Rows can be easily added even in the middle of the table.
I have put the level codes into a named range so that I and O are not included. This can easily be expanded for additional levels by say adding in lower case letters.

Ooo eee ooo ah ah ting tang walla walla bing bang
Ooo eee ooo ah ah ting tang walla walla bing bang

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close