How do I create a formula to remove dashes?
in [General]
|
Prev: Count number times text appears in string
Next: Problem with Paste and Automatically repeating values - no fill ha
From: Pete_UK on 5 May 2010 10:04 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 <jackiera...(a)discussions.microsoft.com> 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!!
From: Ms-Exl-Learner on 5 May 2010 11:34
Assume that you are having the data in A1 cell. Copy the below formula and paste in B1 cell. =IF(A1="","",IF(VALUE(MID(TRIM(A1),2,1))=0,LEFT(TRIM(A1),1)&SUBSTITUTE(SUBSTITUTE(MID(TRIM(A1),3,255),"V",""),"-",""),SUBSTITUTE(SUBSTITUTE(TRIM(A1),"V",""),"-",""))) Remember to Click Yes, if this post helps! ------------------------- (Ms-Exl-Learner) ------------------------- "jackierae17" <jackierae17(a)discussions.microsoft.com> wrote in message news:CC3C8AF8-DBEC-444A-99AE-2AE62CBCD181(a)microsoft.com... > 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!! |