Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Deleting content
Hello there,
I am working with an excel sheets that has over 3000 products in our inventory The first column contains the part number. Each part number has a three letter code (different for each vendor we carry) followed by a space and then the part number. My question is, is there ANY way to delete the three letter code plus the space all at once without having to use the find /replace option for EACH vendor? Thanks in advance for any help you can give me! Alissa |
#2
|
|||
|
|||
Assuming your part number is on column A On column B type
=mid(a1,5,1024) and copy is down Then select column B - copy-paste special Values you can delete col A now if you have to "alazydaydreamer" wrote in message ... Hello there, I am working with an excel sheets that has over 3000 products in our inventory The first column contains the part number. Each part number has a three letter code (different for each vendor we carry) followed by a space and then the part number. My question is, is there ANY way to delete the three letter code plus the space all at once without having to use the find /replace option for EACH vendor? Thanks in advance for any help you can give me! Alissa |
#3
|
|||
|
|||
Insert a new column A, then use this formula in the new column
=RIGHT(B1,LEN(B1)-4) Note - this will return the number as text, & you may want that if you need to preserve leading zeros. If that's not a concern and you actually want nunbers, use =VALUE(RIGHT(B1,LEN(B1)-4)) Copy the formula down as far as you need it. To convert it from a formula to a value - select the entire column of formulas, press Ctrl-C, then press Alt-E, S, V and hit Enter Duke "alazydaydreamer" wrote: Hello there, I am working with an excel sheets that has over 3000 products in our inventory The first column contains the part number. Each part number has a three letter code (different for each vendor we carry) followed by a space and then the part number. My question is, is there ANY way to delete the three letter code plus the space all at once without having to use the find /replace option for EACH vendor? Thanks in advance for any help you can give me! Alissa |
#4
|
|||
|
|||
Select the column with the part numbers, then:
<Data <Text To Columns Check "Fixed Width", then, <Next Click on, and drag the break line to separate the data as you wish, then, <Next You can now decide on several options: 1 To keep the original column of data, and create 2 new columns of *separated* data, click in the "Destination" window and enter the address of the column to start receiving the separated data (ie, G1), then click <Finish. 2 To separate the data into 2 separate columns, starting in the original column, click <Finish 3 To "throw away" the 3 letter code, and keep the part number *only*, in the original column, make sure the code section is selected (black), and click in "Do Not Import Column". You'll see the header change from "General" to *Skip*. Then click <Finish There are a few more options which I'm sure you can figure out from just these examples. -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "alazydaydreamer" wrote in message ... Hello there, I am working with an excel sheets that has over 3000 products in our inventory The first column contains the part number. Each part number has a three letter code (different for each vendor we carry) followed by a space and then the part number. My question is, is there ANY way to delete the three letter code plus the space all at once without having to use the find /replace option for EACH vendor? Thanks in advance for any help you can give me! Alissa |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro on cell content | New Users to Excel | |||
Footnote wizard linked to cell content | Excel Worksheet Functions | |||
content does not stay in page break | Excel Worksheet Functions | |||
Deleting content in a column | Excel Worksheet Functions | |||
Deleting content in a column | Excel Worksheet Functions |