Using Excel 2000 on Windows XP Home Ed. - Example: Several cells in a column have the same information, i.e., actual miles followed by "mi". I use my Palm Pilot to enter my beginning & ending miles. When I enter the expense catagory as milage, it uploads to Excel with "mi" after the actual milage. That creates several cells with "mi" following the actual milage. When I want to create a formula to total the business miles for the day, I have to delete the "mi" from the cell in order for the formula to work. My question is this: Is there a way to hilite all the cells in the column & remove the "mi" without having to do each cell individually? Or, can you create a formula to remove the "mi" automatically?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Deleting a portion of multiple cells in a column in Excel
- Thread starter bobnplano
- Start date
Storyteller
Instructor
Hello bobnplano,
If the data is like this 67 mi
I would suggest using "Text to Columns" which is under the Data menu.
1. Select the entire column
Note: if there is data in the next column over you will need to insert 1 or 2 blank columns to handle the data.
2. Data then select Text to Colums
3. Follow the Wizard and make sure you select "Space" as the delimiter
This should seperate the 67 and the mi into two seperate columns. The beauty of this is that it can be used more than once for the same column of data and it does not require a formula (=left(A1,Len(A1)-3) for example).
Hope this helps.
Michael
If the data is like this 67 mi
I would suggest using "Text to Columns" which is under the Data menu.
1. Select the entire column
Note: if there is data in the next column over you will need to insert 1 or 2 blank columns to handle the data.
2. Data then select Text to Colums
3. Follow the Wizard and make sure you select "Space" as the delimiter
This should seperate the 67 and the mi into two seperate columns. The beauty of this is that it can be used more than once for the same column of data and it does not require a formula (=left(A1,Len(A1)-3) for example).
Hope this helps.
Michael
bobnplano,
You can highlight all the cells and use the Edit, Replace function. Put in " mi" (note the space before the mi) without the quotes in the Find What line and don't put in anything in the Replace With line and select Replace All.
Hope this helps.
You can highlight all the cells and use the Edit, Replace function. Put in " mi" (note the space before the mi) without the quotes in the Find What line and don't put in anything in the Replace With line and select Replace All.
Hope this helps.
- Thread starter
- #4
Michael & bkpchs237,
Thank you both. Both of those work perfectly. Don't know why I didn't think about highlighting & using the replace function. That sounds so simple & works so simple. Although I like to experiment, I have found myself much too busy to experiment & felt it was too frustrating after 10 or 11 hours a day & at month's end I just couldn't think anymore. Believe me, I certainly do appreciate both of you.
Bob![[2thumbsup]](/data/assets/smilies/2thumbsup.gif "[2thumbsup] [2thumbsup]")
Thank you both. Both of those work perfectly. Don't know why I didn't think about highlighting & using the replace function. That sounds so simple & works so simple. Although I like to experiment, I have found myself much too busy to experiment & felt it was too frustrating after 10 or 11 hours a day & at month's end I just couldn't think anymore. Believe me, I certainly do appreciate both of you.
Bob
- Status
Similar threads
- Locked
- Question