Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


Removing Prefix From Part Numbers

I have a huge column of data. This data has few prefixes that I need to remove. I have a list of possible prefixes. Some prefixes are 1,2,3 or 4 characters long. Could you please suggest best way of removing these prefixes (VBA if possible)?

Following are some of the examples of prefixes:

View Complete Thread with Replies

Sponsored Links:

Related Forum Messages:
Removing 1- Prefix In Phone #'s
I read the thread below on how to utilize the Subsitute function to remove periods and thought about being able to use it for this. However, I have some phone #'s in my list that contain multiple 1- scenarios in them because the area code or 3-digit prefix sometimes include a 1- also. How do I make the formula only look at the 1- for long distance and not any other 1- found in the phone #? I want to remove all of the 1- for long distance because we are trying to use a new autodialer that is pre-programmed with the 1-.

View Replies!   View Related
Removing Prefix On Email Address To Leave Company Name In Field
I have a question regarding deletion and replacement in a new field. ie. A1 reads I want A2 to read microsoft. What is the code to delete "dave.jones@" and ".com" to be revealed in a new field.

View Replies!   View Related
Extract Numbers And Prefix Letter
I need to extract (and then use for SumIfs) only item numbers from the long description. Please see the attached list where item number column shows existing list & next column shows what i want to extract. The exrtacted part if has any trailing or succeeding letters, characters between numbers should stay. for example from "SGA:RV-SVA:PEPPERS/PEPPERONCINI:SV9176001/232034" I need to extract " SV9176001/232034" or from " SPICES:BULK SPICES 7100:9054B" I need to extract " 7100:9054B". Can some one please urgently help me on this.

View Replies!   View Related
Adding Zeros To The Prefix Or Certain Numbers
Im basicaly pulling a report wherein the details will have a column as number.

It basicaly should be a three digit number. The tool will have the information as below.


But when im pulling out the report into excel, the zeros in the prefix goes away and the report looks like the below


I just checked the tools previllages and it seems that we cannot do anyting in pulling the data as it is in the tool.

So wht im trying to do is to save the report in a folder where i have placed a validation sheet which will have references of the parent report.

So when i open the report the reference will pull up the data and in here i would like to add a validation which will add "0" in the prefix if VNO is a 2 digit number or "00" if the VNO is a one digit number.

View Replies!   View Related
Multiple Lookup: List Of Part Numbers Based On The Product Part Code
I'm creating a worksheet that gives a list of part numbers based on the product part code. In most cases I can use the following.

=LOOKUP(O6,{0,1,2,3,4},{"NONE (M25)","SMP-55-001","SMP-55-004","SMP-55-008","SMP-55-014"})

so this gives a part number depending on what number is placed in O6. What I need to do know is look at 2 different cells and for each combination of numbers give a different part number. so if A1 is 2 and B1 is 3 give a certain result.

View Replies!   View Related
Replace One Prefix With Another Prefix
UDC 01_001 template black.jpgBatch 01_385 template white.jpg
UDC 01_001 template blue.jpgBatch 01_385 template blue.jpg
UDC 01_001 template grey.jpgBatch 01_385 template grey.jpg
UDC 01_001 template pink.jpgBatch 01_385 template pink.jpg
UDC 01_001 template white.jpgBatch 01_385 template black.jpg

I have 100s of urls with prefix as the left column. I need to rename them to the right column format. UDC 01_001 will take number Batch 01_385, UDC 01_002 will use Batch 01_386 and so on.

I cant figure out how to do this in one quick macro instead of replace function.

View Replies!   View Related
Removing Text From Numbers
I have a column of data which has numbers and units (small example below).
I need to remove the text (units) and at the same time multiply the number by a value which is based on what the text is:

-999.9uA needs to become -999.9*10^-6

-98.40mA needs to become -98.40*10^-3

View Replies!   View Related
Removing Numbers From Cells With Text
I have a bunch of cells that have City and Zip Code combinations.

Ex: Chicago 606
Fayettville 72701

Some of them are 3 digit zips and others are 5 digits. I just want to weed out anything with a number leaving the city names.

View Replies!   View Related
Removing Text And Leaving Just Numbers
i have a sheet dealing with part numbers and a paramiter exported from a programme.

L=1000MM | L/R is an example.

i use replace to get rid of everything before the number but anything after the mm is totaly different most of the time.

is there a way that i can get rind of anything after, and including, the mm?

or by some chance a command that will just leave me with the number allone?

View Replies!   View Related
Part Numbers With Zero As First Number
I am trying to do a VLOOKUP on a worksheet with a list of our Part numbers. The Part numbers begin with zero and go into the alphabet with anywhere from 3 digits upto 18.

When I sort the sheet Excel sorts the numeric by the number of digits in the number AND totally ignores the first zero. I can not format as numbers since again Excel drops the first zero. Therefore, when I do the VLOOKUP it will not look through the entire numeric list for the higher digit numbers. Is there anyway to resolve my VLOOKUP issue with indexing or?

View Replies!   View Related
Eliminate Endings From Part Numbers
I am trying to find a way to eliminate endings off of part numbers.

Below are the list of endings that I need to be eliminated from an extremely long list of variant part numbert. This list below may need to have additional endings added, but nothing will be removed. Please see attachment for example of part numbers that need below endings eliminated. Thanks!


View Replies!   View Related
Count How Many Orders Have Part Numbers
I am trying to count how many orders have Part numbers A and B on the same order. If order 123 has part number A and B on it then return true or else false. I think Match and array might be a way to go but I am still not able to come up with the result.

Here's how the Data looks like:

OrderPart numbers

Answer for above would be 2 orders that have A and B part numbers on the same order.

View Replies!   View Related
Sorting Alphanumeric Part Numbers
I have a fairly large list (1200 rows) of part numbers that I would like to sort. The part number has text and numbers, with a number in the middle. I need excel to ignore this middle number when sorting (but not any of the other numbers). Excel currently sorts like this:

MKDSN 1,5/ 3
MKDSN 1,5/ 3-5,08
MKDSN 1,5/ 4
MKDSN 1,5/14-5,08

when I want it to sort like this:

MKDSN 1,5/ 3
MKDSN 1,5/ 4
MKDSN 1,5/ 3-5,08
MKDSN 1,5/14-5,08

The first few letters in the part number or the numbers at the end aren't consistant. But it's always the numbers after the "/" and before the "-" (where there is one) that I want to ignore.

At the very least I would I need the list in alphabetical order. I don't need the list to be sorted by the "ignored number" at all. Meaning, I would be happy with this result:

MKDSN 1,5/ 3
MKDSN 1,5/ 4
MKDSN 1,5/ 2
MKDSN 1,5/14-5,08
MKDSN 1,5/ 3-5,08
MKKDS 2/24
MKKDS 2/20-3,5
MKKDS 2/ 2-3,5

I don't mind getting rid of "/" or spaces or "," in the part number, but I would prefer to not split the part number between two columns.

I tried creating a custom list, but there are just too many varieties to list them all.

View Replies!   View Related
Remove Duplicate Part Numbers
I need to remove duplicate Part Numbers where other information in the cells will not match. In the following two examples, the only difference is that the COMP_ID: number is different, but for my purposes, the second example is a duplicate and needs to be removed. I have hundreds of rows of this type of information with various part numbers: ...

View Replies!   View Related
Formula To Identify New Part Numbers
Im using a formula to identify new part numbers. The formula is: =IF(ISNUMBER(MATCH(A217,Existing!A:A,0)),"","NEW"). However you can clearly see from the attached that if has flagged a duplicate part number as new. Why would it do that? Check out A1368 in existing and A217 in new.

View Replies!   View Related
Grouping Same Part Numbers And Adding Their Prices
I have the following Macro which groups same part numbers in an excel sheet and also creating a blank row after each (or set of same) part numbers. Their prices are in the adjacent cells. I want to add the price cells and the total to appear in the blank cell below the prices.
I have one part number in Cell say A1, Its price in Cell B1
i have another same part number in A2, its price in Cell B2
(The macro has grouped them together)
Now I want to add cell B1 & B2 and the result in B3 (Row 3 is blank, created by a macro after each group of similar part numbers)

This process is to be repeted in the entire worksheet.

Sub InsertRow_At_Change()
'part number
Dim LastRow As Long
Dim X As Long
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
Application.ScreenUpdating = False

For X = LastRow To 3 Step -1
If Cells(X, 1).Value Cells(X - 1, 1).Value Then
If Cells(X, 1).Value "" Then
If Cells(X - 1, 1).Value "" Then
Cells(X, 1).EntireRow.Insert Shift:=xlDown
End If
End If
End If
Next X
Application.ScreenUpdating = True
End Sub

View Replies!   View Related
Retrieve Drawing Numbers Associated With Part Number
I have a list of part numbers and a new drawing number and old drawing number. I'm am trying to put this list on the second page of a workbook and write a code on thie first page that allows a person to enter the part number and the old and new drawing numbers will be displayed.

View Replies!   View Related
Group Part Numbers, Blank Line Btwn Unlike
i have a spreadsheet with 21,000 part numbers. I am trying to group the like part numbers, then leave a space between the unlike part numbers. right now my spreadsheet has a space between each part number and i want to eliminate that. but also keeping the part's qty, date, etc. with it.

View Replies!   View Related
Filter Sheet Based On Certain Digits In A Column Of Part Numbers
I am trying to filter my excel sheet based on certain digits in a column of part numbers. The part number has 10 characters. I would like to filter it so that all part numbers where the 4th character is the number 5 or 7 is listed and where the 5th character is a zero. (i.e. R4X5831310 is a part number where 5 is the 4th character; I would like the filter to show this part number)

View Replies!   View Related
Add A Text Prefix
I have a colummn of data (text data). For my purposes I would need to add a prefix text to every single cell contained in that column; for instance, if I have a cell with a text "hellohowareyou" ,

I would need to change it by adding to it a constant prefixation text "es_ES@" so that the final result would be displayed like "es_ES@hellohowareyou" , and so on for the rest of the cells under the same column.

View Replies!   View Related
VBA - File Prefix Only?
Say I have a workbook called test1.xls, where the number 1 at the end of the file name is variable. Tomorrow that character could be 2 so the file would become test2.xls etc (or could even be a letter for what it matters).

Is there a way in Visual Basic to recognize that the final character is not fixed but can get different values each time? Or in other words to recognize that the file starts with "test", and that whatever follows that prefix is not relevant?

For example....

If = "'test*.xls" Then...

where * (star) could be any character.

As a Windows analogy, I am thinking of the * (star) character when one searches a certain directory for, say, Excel files (*.xls etc). Obviously, in VBE that doesn't work, and the star is just a character like any other.

View Replies!   View Related
Trying To Use IF Statement For Prefix Only
Iím making a database to forklifts. Most used code I have found from this forum. I havenít found a way how to copy existing formulas on summary sheet so that sheet name is also changed in formula. I attached also my workbook, but itís not in English language but in Estonian. I hope that isnít problem.

If pressed button on summary sheet (ďPealehtĒ) then existing blank sheet are copied and renamed. On userform1 is textbox3 which value added to summary sheet, also combobox4 value added to summary sheet. Now I want that if pressed commandButton1 on userform1 then all formulas what exist on summary sheet from F6 to CW6 are also copied to next row and in formula sheet name changed. My second question is what to write that if new sheet created then it goes always to end of existing sheets. Right now Iím using code what copies new sheet after blank sheet but itís not satisfying me.

View Replies!   View Related
SUMProduct (182 Matches Part 1 Of Which 32 Match In Part 2)
Using the below formula. I know that I should get the result of 32 if I am dong this correctly (182 matches part 1 of which 32 match in part 2).

=SUMPRODUCT(--($A$1='7. PM BDE'! $J$3:$J$366))*(--('7. PM BDE'!$L$3:$L$366=1))

View Replies!   View Related
Making Sure Cell Has 'J' Prefix
i have a cell "h23" that must always start with a "j" or a "J"
the trouble is some of my users are only putting in the number
ie 2345 when it should be j2345 or J2345

View Replies!   View Related
Filtering In Dropdowns With A Prefix
I'm not a novice but this might be something simple I didn't realize. I am creating a quoting sheet for a steel fab company. I have created dopdowns for the different materials and sizes but the dropdown is way too big. I need to have a column that helps me filter the dropdown more so I don't have to scroll through litterally hundreds of steels to find the one I need. The good news is that most steels have prefixes that make them easier to filter.

For example:




What I'm looking for is a way to have a dropdown that has all of the prefixes (L, C, MC, W, PL, etc) in it and when I select one of them the next column (the actual description of the steel as shown above) will only give me the specific steels for that steel shape (L,C,W,etc)

Currently I'm putting one row of say the MC shapes and one row of the C shapes and one row of the L shapes then copying them as needed. It's a lot of work and you can easily make a mistake.

View Replies!   View Related
Vlookup Based On Prefix
I have a table like below
1 x1234 value
2 y1234 value

If I want to look for all values that start with "x", is there a way to do it within a vlookup formula? Something like: vlookup("x*", A1:B2, 2, False). I know there's other ways to do this, but I want it all contained in one formula instead of splitting the A column using LEFT(A1, 1).

View Replies!   View Related
Delete Worksheets With Specified Prefix
I have several excel workbooks with many worksheets (over 500 in some). Around a third of these worksheets are named "Exp1", "Exp2", "Exp3", etc.

I would like a macro that will delete all worksheets that do not have names starting with the letters "Exp". I do not want any confirmation dialogue, and as the workbooks vary in size, I would like it to finish when there are no sheets left (except the Exp ones of course).

View Replies!   View Related
Prefix Text On Entry
I have often used Data Validation List to create selection lists for cells in a worksheet. The problem is the list has to be on the same sheet you want to use it on. Is there any other way to do this so one common list can be used for all sheets? I have a list of accounts I want to use on 12 different Monthly tabs.

View Replies!   View Related
Prefix Cells With Text
I want to add "CR" to the beginning of every cell that already has data. I know I could do =a1 and it would copy all the way down. However each cell has different data and I'd like to keep it that way but add 2 letters in the beginning.

View Replies!   View Related
Add Prefix To Selected Cells
for a small online database I have a column that lists nationalities:

Latin American
(etc etc)

I need to add a prefix to all nationalities, for example:

Artist Nationality///French
Artist Nationality///Spanish
Artist Nationality///American
Artist Nationality///Latin American
(etc etc)

Is there a way I can select the 700 cells and do this in one shot? I can't add formulas because I will have to paste all this in text pad and then upload it.

View Replies!   View Related
Random 3 Letter Prefix Form (left, Right, Mid)
i need to create a random 3 letter prefix form eg. British Head Office, how would you pick letters out and compare the 3 letter prefix back to a column with existing prefix. and if u can help me even more how can i automate this method beacuase i for eg. get like 50 company names at a time and dont have time to do and check them 1 by 1 lol.

View Replies!   View Related
Sum Values With Names Based On A Number Prefix
How can I sum values with names based on a number prefix? For example, in the attached sheet, how can i sum all values that have the prefix 4.10.02.xxxx?

View Replies!   View Related
Naming Sheets With Time-dependent Prefix
I have got this macro working OK but now I need to add a 2 letter prefix/suffix depending on what time it was created (am or pm). Detail: If the sheet is created between 0600hrs and 1800hrs then "DS" and likewise between 1800hrs and 0600hrs then "NS". Outcome: The sheet will then have a name like - "22 Mar DS"...code included below

View Replies!   View Related
Lookup Function: Compare Every Part In The All Parts Worksheet To See If The Part Number Exists On The Active Parts Sheet
I have a spreadsheet with 2 worksheets. On the first "active parts" I have a list of active part numbers and on the second "All Parts" I have all of the parts available.

I want to compare every part in the All Parts worksheet to see if the part number exists on the Active Parts sheet - if it's there, I would like it to return the value "Active" in column B in All Parts. I have a formula in column B in All Parts that seems to work for the first few, but as soon as it finds one that is active, the rest of the cells below all return "Active".

View Replies!   View Related
Prefix Negative Sign To A Positive Numeric Character
The report generated from our co. software in excel have negative figures suffixed as "-" for ex., payable shown as 21373533-

- The value is treated as text in excel

- How should i Bring the negative sign for this type of data as prefix
when there is huge data and also in a columnar manner.

View Replies!   View Related
Run Part Of Macro, Pause, Run 2nd Part
I have 2 basic parts to a Macro which need to be separated by a pause of 1 second, before proceeding to the next part of the macro. Then pause for 1 second, and loop.

Application.Wait is no good to me as the spreadsheet must remain live, editable, and receiving updates from an external program linked via DDE. Below is the ...

View Replies!   View Related
Copy Data From One Part# On Sheet 2 To The Same Part# On Sheet 1
What I am trying to do is automatically move the date received and the total qty received from the Rcvg. Log sheet to the RFMs Release sheet.

The problem is:

1) I don't know what I'm doing

2) On the Rcvg. Log several part numbers that are the same with different Qtys.

3) on the Rcvg. Log the are several part numbers that are the same using different programs.

What I would like to do is combine all the same part numbers with the same program add the total Qty received and paste the date received and total Qty into the RFMs Release sheet in the proper part numbers row.

Is this possible?

Attached is a shortened copy of the spreadsheet normally there about 3000 entries.

View Replies!   View Related
Add-In Not Removing
So I created a custom Add-In, but the add-in isn't removing itself when I uninstall it or when I close the program (this creates an error in saving files, so all files are getting corrupted)

This is all the code I have running the menu bar and I can't figure out the proper closing code

View Replies!   View Related
Removing #DIV/0! ..
I have a formula in a workbook that keeps giving me #DIV/0! no matter what I do. I am trying to total up a column of numbers. If I enter into B28 the following formula : =SUM(D4*E4)+(D5*E5)+(D6*E6)+(D7*E7)+(D8*E8)+(D9*E9)+(D10*E10)+(D11*E11)+(D12*E12)+(D13*E13)+(D14*E14 )+(D15*E15)+(D16*E16)+(D17*E17)+(D18*E18)+(D19*E19)+(D20*E20)+(D21*E21)+(D22*E22)+(D23*E23)+(D24*E24). it does not give me the #DIV/0! but the result is incorrect.

what I need is a formula that totals rows 4 through 24 but the formula for each row individually would be =(d4/b4*e4) but I am trying to avoid having to add multiple extra columns (& clutter) for each item in each row that I need to calculate. I have attached the workbook so that you can see what I am trying to do.

View Replies!   View Related
Removing Text
i would like to take the first letter of a cell and add 1000 to it.

At the minute, ive only been able to take away the first letter which isnt what i want to do

so far i had

View Replies!   View Related
Removing Decimals
i have columns of prices in 2 decimal format that I'd like to manipulate, some prices are whole dollars(no cents & no decimals ) and most are dollars and cents in decimal format-- but to do it successfully, i need the decimal removed. I looked at the "format" function but it doesn't seem to allow for that.

is there any way I can have these prices converted to "cent" format( ie removing the decimal, where applicable), showing them not as "dollars & fractions" but as "cents" i'm sure that would solve the problem for me last time I did it by hand and vowed there had to be an easier way!

View Replies!   View Related
Removing Modules
It seems like I am asking a question at least once a day. I searched the net and forums and could not find an answer to this question. Thanks to everyone for the help I am making a lot of progress because all of you. Basically, I have a read-only workbook which the user gets a prompt to save as a new workbook for editing purposes. I have a autorun macro so now when the user opens their workbook, it contains my autorun macro. I do not want this. Is there a way to remove modules so that the users copy has no macro information?

View Replies!   View Related
Removing Symbols
I have lists of phone numbers with parenthases and hyphens. like (555) 333-4444

I would like to remove all symbols and leave just the numbers.

I would alsolike to add the number 1 in the first position.

so (555)333-4444 would end up 15553334444

View Replies!   View Related
Removing Duplicates
I have a spreadsheet which contains 2 columns of data, most of which are duplicates.

I'm looking for a macro which will check all of colum A (A2:A138)


Column B (B2:B163)

I would like the macro to remove duplicate entries (from column A) in column B so that all that is left in column B are entries which don't match any in column A

View Replies!   View Related
Removing Cells ...
i am removing cell contents using fallowing code

Private Sub CommandButton1_Click()
Dim row, col
row = ActiveSheet.UsedRange.Rows.Count
col = ActiveSheet.UsedRange.Columns.Count
For i = 1 To row
For j = 1 To col
Cells(i, j).celarContetents
End Sub

but next time the cell count is showing previous rows value?

View Replies!   View Related
Removing Npsb
I have a string in each cell in range D32:J55.

The second, third or fourth character at random is a npsb 'ALT(0160).
how I can keep everything left of the npsb only.

Data looks like this.

5 (1)
23 (4)
10 (5)
101 (3)

View Replies!   View Related
Removing Rows With 0
I was wondering if anyone had a formula or Macro suggestion to remove rows with no information. I made a pivot table that feeds to another sheet in order to make it more user friendly. The only thing is when there is no information I have formula to returns a zero. I would rather the row be hidden.

View Replies!   View Related
Removing And Replacing
how i would remove telephone numbers in a particular spreadsheet which are preceded with 44 which need to be removed and then replaced with a zero.

eg. 441234567890

View Replies!   View Related
Removing #DIV/0 And Returns The Value Zero
I am Facing is I get this error as #DIV/0! when i use the formula where fields are blank or 0.

The Formula is: -

What changes can i do in this formula that it calculates exactly as it is doin now but instead of #DIV/0! error it returns the value 0.

View Replies!   View Related
Removing #DIV/0! Where Cell Value Is Zero
I have the following forumla....


in those three cells there is a value of 0

the following message appears in the formula cell - #DIV/0!

is there a way to change my formula so it doesn't display this message and just return 0

View Replies!   View Related
Copyright © 2005-08, All rights reserved