Email:      Pass:    Pass?
Close Window   
TE
Subscribe for Email Updates!
Excel tips, help, and more!
E-mail:


Advertisements


Free Excel Forum

Getting Rid Of Hyphen

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

I am working wit ha other price list, this time a vendor sent me a price list with a description and part number, the problem is that when I copy there part number there is alway's a invisible hyphen, this is creating havock when I try to create the part numbersin my system usinf excel.

Here is a example, when you look at the box you see 377-2747, but when you click on it you then see '377-2747.

I tried formating to general, I tried copy paste special values and the darn hyphen is still there....

Can anyone help me....


Similar Excel Video Tutorials

Helpful Excel Macros

Replace Formulas with Values (For The Entire Workbook)
- This macro will convert every formula in an entire workbook into its respective value. This is basically a quick way to
Format Cells in The General (default) Format in Excel Number Formatting
- This free Excel macro formats a selected cell or range of cells to the General number format in Excel. This is the defa
Create a Bar Chart With a Macro in Excel
- Create a bar chart in Excel with this macro. You will be able to quickly and easily turn any range of numbers and data
Add a Drop Down Menu to a Cell in Excel - Data Validation Macro
- Add a drop down menu or list to a cell in Excel with this free Excel macro. This is a great little macro that allows yo
Filter Data Sets on Multiple Columns with Multiple Criteria at Once in Excel - AutoFilter
- This Excel macro allows you to filter a data set on multiple columns and criteria at once. This means that you can filt

Similar Topics







Hello,

I Don't know where to really start so here goes.

I have a list of parts on one sheet that includes the following (sheet name is: Products):
Part Number, Part Description, Part Price.

On another sheet I have a job (sheet name: Job) that has different parts depending on the job, on this sheet I currently hand enter the part number, part description, part price and quantity, there are hundreds of different parts I have to enter, so currently I copy and paste them. I would like to just type part numbers into the job sheet, highlight them (only certain ones) and then hit a button that would bring the product description and price into the other columns.

I know its a tall order but I would like to learn VBA and would really appreciate any help.


I have a 2,000 person address list with columns for name, street, city, etc. The zip code column lists the entire 9 digit zip code with no hyphen. I can't mail merge it into publisher for a mailer. How do I add a hyphen after the 5th number to break up the zip code?

EX:
Zip
305189999
30518-9999


Hi my code is

Code:

 
If Instr(1, Sheets(i).Name, "-")=1 then
     Msgbox "hyphen"
else
     Msgbox "no hyphen"
end if


a list of my worksheet names include

WAVE998
SA43-54-54
SA34-6.5-23

etc

and I get the msgbox "no hyphen" on all of them!


I have an Excel sheet with a list of part numbers and vendors associated wtih those part numbers. For example:

Part Number Vendor
Part 1 A
Part 1 B
Part 2 A
Part 3 B
Part 3 C
Part 4 A
Part 4 B
Part 4 C
Part 4 D

When I run a Pivot Table, using 'Count of Vendor' only returns the number of vendors for each part number. For example:

Part Number Count of Vendor
Part 1 2
Part 2 1
Part 3 2
Part 4 4

However what I NEED is to output the vendor names for each part number in separate column cells. For example:

*******************************************
Part Number Vendor Vendor Vendor Vendor
Part 1 A B
Part 2 A
Part 3 B C
Part 4 A B C D
*******************************************

How can this be achieved? I've tried various 'Value Field Settings' and changing number format to text, but no dice. There has to be a way to do this...


Hi,

I have a scenario where I have to ensure all Apartment unit numbers are consistent since they are referring to the same type with the maximum length 9 alphanumeric including 2 hyphens and the position of hyphen always located after the number e.g. C-07-02 or C5-07-02 or C5-13A-13
Further, alphabet "A" is always located after the number e.g. 13A or 03A

E.g. No1
Col A Result
C-7-02 ---------> C-07-02
C-07-2 ---------> C-07-02
C-7-2 ---------> C-07-02
C-07-02 ---------> C-07-02 (ok)
B3-7-10 ---------> B3-07-10
B3-11-5 ---------> B3-11-05
B3-11-05 --------> B3-11-05 (ok)
B3-11-15 --------> B3-11-15 (ok)

E.g. No2
Col A Result
A1-13A-8 --------> A1-13A-08
D-03A-6 --------> D-03A-06
D-3A-06 ---------> D-03A-06 (ok)
D-3A-6 ---------> D-03A-06
B5-13A-15 --------> B5-13A-15 (ok)

In this case, how to use excel formula or VBA to fix the following in order to achieve the above results :
1) the last 4 digits number after 1st hyphen to the left under example No1

07-02 for 7-02,07-2 and 7-2
07-10 for 7-10 1
11-05 for 11-5

2) the last 4 digits number with 1 alphabet "A" after 1st hyphen to the left under example No2

03A-06 for 03A-6, 3A-06 and 3A-6
13A-08 for 13A-8

Any help will be much appreciated

Thanks

Regards
Len


Please see attached file. In my column L, I'm trying to find a formula that would reference my column J to the table in the data tab. My problem is the data in column J has a hyphen (-) in the number ex.. 047-258-1 and the next tab does not have a hyphen in between the number...ex 0472581. The correct results in cell L3 would be Walmart. How can I adjust my formula to accommodate this hyphen. Can this also be reflected in my pivot table as well?

Thank you for your assistance.


Ok, so I have a parts list that need prices updated. My parts lists are on multiple worksheets and the same part might be listed multiple times on different sheets. The first 8 worksheets or so are parts list and the 9th sheet contains a dynamic list that shows me all the parts in the entire workbook without duplicates organized by vendors. Basically when I get a quote in from a vendor I would like to go to the 9th sheet and select that vendor then input the prices for all parts associated with that vendor. The thing is I want to put the price in once for any given part and have it find all of those parts on sheets 1-8 and update the pricing for them also. Below is an example of a 9th sheet part... I can select a vendor and the list automatically updates with all the parts associated with that vendor. I would then type in the price and would want all my other sheets to update with that pricing for the associated part number. Then I would select another vendor and again put in price a price. I want the prices on all the worksheets to stay, not go away when I select another vendor. This might be a little confusing but hopefully there is a solution. This will make my life a lot easier...Help!


Select Vendor: CompanyX

P/N...................Description................U/M..........Qty..........Comments...........Quoted Price
2-25110-9.......ROUGH FORGING......EA............10.............None.....................$19.21


I'm trying to do the following in a column. Find the second hyphen exist if not, replace it with a second hyphen. For eg. this is correct, because the second hyphen comes after the 2 digit year

MER-BLM09-NYC


This is wrong..
MER-M09

For our logic to work, we need to figure out if a second hyphen exist and if not, needs to fill it in.

Have around 2000 records and this is done very manually currently. Wanted to get some suggestions on what's the best way to automate this.

Thanks
Ajay


I will try to make this as clear as I can. It is quite a challenge and I
have no idea how to do it.

- Data is received from client, so I have to control over the content
- I need to quote on part numbers that may be supplied by multiple vendors
- IDEALLY the data is as follows:
Description Part Number Vendor Qty Price
Widgets 1111 ABC 500
.25
Widgets 2222 XYZ 500
.25
Widgets 3333 MMM 500
.25

However, often the data is received like this:
Description Part Number Vendor Qty Price
Widgets 1111 ABC 500
.25
2222 XYZ 500
.25
3333 MMM 500
.25

Code Required:
If there is a part number with no description to the left of it, copy the
description above so that it looks like scenario 1 above.

Thanks!




--
Thanks!

Dee



I have a 2000 company mailing list in excel. The zip code column has the full 9 digit zip code with no hyphens or spaces. The post office won't take it without the space.

I posted this a little while back and got some good responses. I got the hyphen inserted, but when I drug the list into access, and converted it to a mailing list, the hyphen wasn't there.

I can get the hypen inserted based on the results I got back previously, but when I click on cell, the hypen doesn't show up at the top in the main bar.

Is there a way I can do this and have access recognize the hyphen?

Or, is there a formula to delete the last 4 digits in a column? That would work also, b/c I don't need the full zip, just the main 5 numbers.

Thanks


I copy and paste date from the internet (typically acrobat files) into a spreadsheet. The data is in the format xx-xx or as an example, 12-05, or 25-23, referring to a whole number on the left of the hyphen and the fraction of 32nds on the right of the hyphen. So 12-05 equals 12 and 5/32, or 12.15625. And 25-23 is 25 and 23/32, or 25.71875.
Currently, I have a formula using "mid" and "left" to recognize each portion of the string (12-05, 25-23, etc) as the proper corresponding number. The problem arises in the situations similar to the 12-05, or whenever the whole number is 12 or less. Excel automatically assumes these as months in a date format. Thus, 12-05 is pasted in as Dec-05.

What I need is a formula that gets around this problem. I would prefer to stay away from a macro, as that creates system issues from time to time.

Thanks for your help.


Hello,

Hope someone can advise on this as excel certainly isnt my Forte.

Basically I have a list of part numbers which need pairing up to a price list.

For example, I have multiple of the same part number on one list, and just the single part number and price on another list.

Is there a way to pull the price which corresponds with the single part number, into the multiple part number list in a seperate column? Not sure if ive made sense but will give an example...

sheet 1
A(part number) B(price)
KO1500 - blank
KO1500 - blank
KO1500 - blank
KO1500 - blank
KO1520 - blank
KO1520 - blank
KO1520 - blank
KO1520 - blank
KO1520 - blank

sheet 2
A(part number) B(price)
KO1500 - 45.95
KO1520 - 49.95

Is there a way to pull the price information from sheet 2 into sheet one so it matches all of the same part numbers with the price from sheet 2?

Or perhaps its easier to copy the multiple part numbers from sheet one to say column C on sheet 2 and then use D to match up the prices, so everything is on the one sheet to do the macro.

Any thoughts would be greatly appreciated
Thank you
Sam


Hi,

Can anyone hlep with this. I have a list of cells some of which just have a name and some have a name then a hyphen and some letters and numbers.

Basically I need a formula that will allow me to take out whatever is typed after the hyphen and if possible to replace it with another letter.

ie, on sheet just now I have info like this in the column:
abc
abcfd - df1
abcb - df2
abcs

And I need this displayed as:
abc / TM
abcfc / TM
abcb / TM
abcs / TM

The amount of letters in the list changes but the letters I want to replace the hyphen and details with will always be the same ie TM. Even if I need to do this over two columns, to remove the hyphen and details then somehow ad on the forward slash TM after whatever is written there.

Hope this makes sense.


I need help with merging two Excel files. 2009 Item List.xls file has two columns Part number and Description. Price.xls file has two colums: part number and price. I need to merge them into a file that whould have three colums: part number, description, price. There 6712 rows in 2009 Item List.xls and 4743 rows in Price.xls file. So I need them matched based on the part number, and have the cells blank for the part numbers for which the price was not assigned.
Please help.
Thank you


I was wondering how can I accomplish reading a text that contains a hyphen in the middle.

I copy information from the internet and paste it to Excel.

I need help with TWO problems.

The format it pastes is like this:

123-234 (this is read as text)

I want a formula that I can read these two "numbers" (even though they are paste as text) without hyphen. I want to put first number (123) in one cell and the second number (234) in another cell so I can make my necessary calculations.

What formula can I use for this?


2nd problem.

I also copy another table and paste in Excel and the format is this:

FROM St Louis
TO Las Vegas
FROM Brazil
etc....

The word "FROM" and "TO" never changes, the only thing is the location.

I want to be able to read word FROM or TO as well as the location and put in two different cells.

Like FROM in one cell, and St Louis in another cell, etc

How can I do this?

Thanks in advanced.


I have a column of data that includes a product name along with a code for a
particular container type. The name and code are separated by a hyphen. For
example AB1222-10, AB1222-330OW, AB1222-330OWPCFL.

As you can see, the string to the right of the hyphen varies in length and
it can be either alpha, numeric, or a mix.

How can I generate a column that contains only the text to the right of the
hyphen? Example (based on above): 10, 330OW, 330OWPCFL as cell values in
the column.

Thank you....



A workbook that I created has two spreadsheets

1) Sheet1= RFQ History (includes RFQ#, company name, address, co. contact, quoted Part #, part Description, etc.)

Repeating rows of data in sheet 1 are as follows:

col P= line item 1
col Q= Part Number
col R= Part Description
col S= Qty 1
col T= Price 1
col U= Qty 2
col V= Price 2
col W=Qty 3
col X= Price 3
col Y= Qty 4
col Z= Price 4
col AA= line item 2 (repeat Q-S)

2) Sheet2= RFQ (a form that pulls data from sheet 1 and pastes it into the appropriate cells in sheet2)

On sheet 1, it is possible (but not required) that a particular Part# might have up to 4 price breaks (i.e. qty 50 pcs, price $100 / qty 100 pcs, price $500). When the macro is run, it should capture the part #, part description, qty, and price info from sheet1 and paste this into the appropriate cell in sheet2. Our company may quote up to 15 parts and 4 parts can fit on the quote sheet (see below for template).

Col B Col E col H col J
PN 1 Desc 1 Qty 1 Price 1
PN 1 Desc 1 Qty 2 Price 2
PN 1 Desc 1 Qty 3 Price 3
PN 1 Desc 1 Qty 4 Price 4

SPACE

PN 2 Desc 2 Qty 1 Price 1
PN 2 Desc 2 Qty 2 Price 2
PN 2 Desc 2 Qty 3 Price 3
PN 2 Desc 2 Qty 4 Price 4

SPACE, etc.
The question I have is this: Can I write a macro to recognize the exact number of pieces that are quoted and the corresponding price (with a max of 4 price breaks) so that the data is transferred to the form and a space is left between various part numbers. In other words, if I have only two prices quoted for part 1, I don't want 3 blank rows in between the next part# that was quoted. I want the program to recognize that the prices for PN1&2 should be copied over, then leave a blank row, then copy PN2's prices, etc.


Hello there. Great forum, always been a lurker..now I have a question that I can't seem to solve.

Below is the link to my file.

http://www.2shared.com/file/4598444/...p-pricing.html


I am using a Vlook to find "FIELD 1", I want to use the Vlookup result to find a corrosponding field. How do i do that?










































































































































































































































































































































































Vendor #1

Vendor #2 The Two Tables on the left contain a Part Number, which corresponds to the quantiy and prices from 2 vendors. Part Number Quantity (pcs) Price ($)
Quantity (pcs) Price ($) Quantity (pcs) Price ($)
X1 500 0.25
500 0.26 100 0.25
X2 400 0.30
400 0.25 95 0.30
X3 300 0.35
300 0.24 90 0.35
X4 200 0.40
200 0.23 85 0.40
X5 100 0.45
100 0.22 80 0.45
Z1 450 0.26
450 0.25 100 0.26
Z2 350 0.31
350 0.30 95 0.31
Z3 250 0.36
250 0.35 90 0.36



Z4 150 0.41
150 0.40 85 0.41



Z5 50 0.46
50 0.45 80 0.46







































Enter Part Number Price ($)
Quantity (Pcs)







X2 0.3 Vendor #1 ((VLOOKUP($B$19,A5:H14,2))) See I am having trouble using the Vlookup to find the corrosponding Quanity that would go with the price.






((VLOOKUP($A$19,$A$5:$H$14,lower (Between Column "F" and "H"))) Vendor #2 #N/A















How can I get it to pick the lowest Price ($) from vendor 2?

































What I am eventually tring to achieve is, write something up, so that I can put in a "Part Number" with a quality, and it finds the Vendor with the lowest quatity with the lowest price.






















































































I am attempting to create a simple quotation sheet

I have one cell in Sheet1 validated as list:
C1 - Customer Price-band Category (select from list)

and cells each refering to:
A1:A100 - Part Numbers (select from list)

In Sheet2, I have price lists for each Price-band Category against each Part
Number.

In the cell adjacent to the Part Number in Sheet1, I would like to display
the relevant part price depending on which Price-band Category and which Part
Number has been selected

I would appreciate any thoughts...





I have two spreadsheets that cant be combined. One is a list of serials assigned to part # and order #, the other is a log of activity, with order number, part #, serial, completed date, etc. What i am trying to do is when i enter the order number in the log, i want is to automatically fill in the part # and range of serial numbers with a hyphen associated with that order number, from the serial spreadsheet. The serials are always consecutive and I already have the part number working with VLOOKUP. Hopefully there is a solution, thank you

I am creating a template for our office to use for quotes. I would like to create a dropdown list which would incorporate 3 columns from another spreadsheet-part #, descr, and price. I would like this to be part of one drop down list-how can I do that. (ie-click on the list and have part #, descr, and price come up at the same time) I would still like to keep each field in its own column, but to fill in the line at the same time.


Fellow Forum Members,
How can the code below be modified so that all characters located to the LEFT of the second hyphen from the left are dropped?

=RIGHT(A1,LEN(A1)-FIND("-",A1)+1)


For the example below:
K00005-RRRRR-10-444-5555-667

The result I'm seeking is:
10-444-5555-667

Because the hyphen that follows RRRRR- is the second hyphen from the left.
And all characters that precede this hyphen is what I'm seeking to drop (in other words the code I'm seeking automatically eliminates the K00005-RRRRR- charcters since they precede the second hyphen from the LEFT).

Any help will be greatly appreciated. Thanks.



I have a column with numbers and letters separated by a hyphen. I need the 1st hyphen and everything before the 1st hyphen removed. Some cells have multiple hyphens. I need to keep the other hyphens if there are any.

631516 - tower a would be tower a
X919 - tower w/o would be tower w/o
117 - tower a-z would be tower a-z
22255 - tower a-z tower aa-aj would be tower a-z tower aa-aj


Thank you

I've got a Vlookup that I run from a macro. When it finds a match is there a way I can find out what row the match came from as well as the value?

For example, I paste a part number into a text box and get back a price for that part number and life is good, but I want to be able to change one of the values used in the calculation. The quantity.

Column H has the formula that uses fixed data for that part number and a variable (Quantity) to calculate the price. If there is 1 part, it might cost $100.00, but if there are 10 parts, it might only cost $20.00.

I want to be able to enter a quantity into a text box and have that value put into the cell of Column D in the same row the vlookup found a match in.

Part Number PDF Description Qty SU Run Mat'l $Ea $Tot 3603840840 Yes Description 15 4 30 $ 3.11 $ 49.11 $ 736.65 3604136215 Yes Description 390 2 10 $10.00 $ 20.31 $7,920.00 3607842215 Yes Description 15 5 115 $16.61 $151.61 $2,274.15 3607865590 Yes Description 15 8 78 $25.00 $135.00 $2,025.00 3607914465 Yes Description 30 8 180 $10.00 $206.00 $6,180.00 3607939965 Yes Description 31 10 180 $17.44 $216.79 $6,720.64

Above is the format I use in the spreadsheet. I search for the part number, then use Qty, SU, Run and Mat'l to get $Ea.

SU, Run and Mat'l are fixed values, Qty is not. Changing Qty will adjust the $Ea and $Tot values. I want the user form to take the value I put into a text box and replace the value in Qty, for the part number I found in the Vlookup.

Confused? Me too.


Hello,

I would like to be able to filter out rows within a spreadsheet that contain hyphens:

For example:

A100
A100-S
A100-M

I want to use a formula to highlight if text in a column has a hyphen in it or not.

So I tried to do the following:
=IF((FIND("-",C177,1)),TRUE, FALSE)

The formula return TRUE if the value has a hyphen but returns #VALUE# if it does not.

But I do not know of another function I can use tell if a hyphen is displayed or not.

Ideally if a row contained a hyphen it should display TRUE
If a row does not contain a hyphen it should diplay FALSE

Any help would be appreciated,

Cheers Joe.