Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I create a formula to remove dashes?
I'm new to Excel. I have a huge inventory spreadsheet where I need to change
a column to reflect the following. "P01-02-C-1" should become "P102C1" (remove 0 and dashes) "V1-25-F-2E" should become "125F2E" (remove V and dashes) HELP!! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I create a formula to remove dashes?
highlight the column header (ie C)editreplace - blank
-- Don Guillett Microsoft MVP Excel SalesAid Software "jackierae17" wrote in message ... I'm new to Excel. I have a huge inventory spreadsheet where I need to change a column to reflect the following. "P01-02-C-1" should become "P102C1" (remove 0 and dashes) "V1-25-F-2E" should become "125F2E" (remove V and dashes) HELP!! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I create a formula to remove dashes?
Try
=SUBSTITUTE(SUBSTITUTE(A1,"-",),"0",) Removing 0 and dashes will make that "P12C1" and not "P102C1" =SUBSTITUTE(SUBSTITUTE(A1,"V",),"-",) -- Jacob (MVP - Excel) "jackierae17" wrote: I'm new to Excel. I have a huge inventory spreadsheet where I need to change a column to reflect the following. "P01-02-C-1" should become "P102C1" (remove 0 and dashes) "V1-25-F-2E" should become "125F2E" (remove V and dashes) HELP!! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I create a formula to remove dashes?
Just use Find & Replace (CTRL-H). Highlight the column by clicking on
the column header, then CTRL-H, and: Find what: - Replace with: (leave blank) Click Replace All Hope this helps. Pete On May 5, 2:46*pm, jackierae17 wrote: I'm new to Excel. *I have a huge inventory spreadsheet where I need to change a column to reflect the following. "P01-02-C-1" should become "P102C1" (remove 0 and dashes) "V1-25-F-2E" should become "125F2E" (remove V and dashes) HELP!! * |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I create a formula to remove dashes?
I have the perfect solution for you and it involves two easy steps:
(1) You will use the TEXT TO COLUMNS function to separate each set as : V1 in one column, 25 in another column, F in another column, 2E in another column, etc. (Data: Text to Columns click on "Delimited"select a delimiter: "Other" and type a dash in it (-); click NEXT; click FINISH (this will separate or parse your data). (2) Now you want all of these pieces in one column, so use this function: ampersand. What you do is, go to the cell where you want your set of data, type "=", click on the cell where you have the "V1" and type "&" in the formula, now click on the cell where you have "25 and type "&", and so on forth until you get your final product: "V125F2E". Roxana "jackierae17" wrote: I'm new to Excel. I have a huge inventory spreadsheet where I need to change a column to reflect the following. "P01-02-C-1" should become "P102C1" (remove 0 and dashes) "V1-25-F-2E" should become "125F2E" (remove V and dashes) HELP!! |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I create a formula to remove dashes?
Hi,.
you can use find and replace, highlight the column , press CTRL + H, find what enter -, replace with don't enter anything and click replace all, do the same with the 0 and V "jackierae17" wrote: I'm new to Excel. I have a huge inventory spreadsheet where I need to change a column to reflect the following. "P01-02-C-1" should become "P102C1" (remove 0 and dashes) "V1-25-F-2E" should become "125F2E" (remove V and dashes) HELP!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Remove dashes from phone numbers in a spreadsheet | Excel Worksheet Functions | |||
Why do the preceding zeros disappear when I remove dashes? | Excel Discussion (Misc queries) | |||
how to remove dashes | Excel Worksheet Functions | |||
Remove Dashes from Social Security Numbers | Excel Worksheet Functions | |||
remove the ( ) & dashes from column of phone # | Excel Worksheet Functions |