Hi,
I have an excel spreadsheet with numberous rows of data. Column E, named "Wholesaleprice" contains date listed with the price followed by "USD". $14.00 USD. I need to remove the USD in all of the cells.
Column A, named "ItemName" has cells containing various length phrases. I need to have everything after the first word in each cell removed. The length of the words varies. Each word is separated by a space.
Thanks!
Leesha
Removing info in a cell
-
- Administrator
- Posts: 78549
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Removing info in a cell
1) Select column E.
Press Ctrl+H to activate the Replace dialog.
Enter a space followed by USD in the 'Find what' box.
Leave the 'Replace with' box empty.
Click 'Replace All'.
2) Select column A.
On the Data tab of the ribbon, click Text to Columns.
Select Delimited in the Text to Columns dialog, then click Next >.
Tick the check box for Space (and clear the others), then click Next >.
Leave the first column set to 'General'.
Click in the second column, then select the 'Do not import column (skip)' radio button.
Do the same for the following columns.
Click Finish.
Press Ctrl+H to activate the Replace dialog.
Enter a space followed by USD in the 'Find what' box.
Leave the 'Replace with' box empty.
Click 'Replace All'.
2) Select column A.
On the Data tab of the ribbon, click Text to Columns.
Select Delimited in the Text to Columns dialog, then click Next >.
Tick the check box for Space (and clear the others), then click Next >.
Leave the first column set to 'General'.
Click in the second column, then select the 'Do not import column (skip)' radio button.
Do the same for the following columns.
Click Finish.
Best wishes,
Hans
Hans
-
- gamma jay
- Posts: 25455
- Joined: 17 Mar 2010, 17:33
- Location: Cape Town
Re: Removing info in a cell
For column E, you can use Find/Replace.
Select the entire column E, access the Replace tool and type out " USD" in the Find box (without the quotes; and note to include the space before USD)
In the Replace box, leave completely blank
Click the replace all button.
For column A, you can use Text to Columns to split the column at the first space.
Select the whole column A
On the DATA tab click on text to columns
Leave as delimited in the first step
In the second step, choose "Space" and the delimiter
In the third step click in all the other columns (except the first column) in the preview window and set them to Do not import (Skip)
Click Finish.
Select the entire column E, access the Replace tool and type out " USD" in the Find box (without the quotes; and note to include the space before USD)
In the Replace box, leave completely blank
Click the replace all button.
For column A, you can use Text to Columns to split the column at the first space.
Select the whole column A
On the DATA tab click on text to columns
Leave as delimited in the first step
In the second step, choose "Space" and the delimiter
In the third step click in all the other columns (except the first column) in the preview window and set them to Do not import (Skip)
Click Finish.
Regards,
Rudi
If your absence does not affect them, your presence didn't matter.
Rudi
If your absence does not affect them, your presence didn't matter.
-
- BronzeLounger
- Posts: 1488
- Joined: 05 Feb 2010, 22:25
Re: Removing info in a cell
Thank you both!! Wow that saved me a great deal of time!! And better yet, taught me something new!
Leesha
Leesha