Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
replacing text in a cell
Hello,
I have a file with about 5,000 records. A majority of them are fine, but some of them have an * in the cell. This prevents my query from matching up with another file, because there should not be a * in the cell. so for example one file contains this product id: FRK F218026* and the other file contains FRK F218026 I have tried to do a replace, but when I replace the * everything disappears. I next tried to filter on anything that contains an *, but that didn't filter anything. Apparently MS Excel "thinks" that there is an * in every cell. Any suggestions would be greatly appreciated. Thank you, Roger |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
replacing text in a cell
Assuming that the asterisk is always the last character...
Insert a helper column to the right of the product ID and enter the following formula, changing cell addresses as needed: =IF(RIGHT(A1,1)="*",LEFT(A1,LEN(A1)-1),A1) Copy down the column as far as necessary. Copy the entire contents of the helper column, move to the first cell in the product ID column and click EDIT in the menu, select PASTE SPECIAL and then click the VALUES option button. Click OK to complete. Delete the helper column when done. -- Kevin Backmann "Roger Converse" wrote: Hello, I have a file with about 5,000 records. A majority of them are fine, but some of them have an * in the cell. This prevents my query from matching up with another file, because there should not be a * in the cell. so for example one file contains this product id: FRK F218026* and the other file contains FRK F218026 I have tried to do a replace, but when I replace the * everything disappears. I next tried to filter on anything that contains an *, but that didn't filter anything. Apparently MS Excel "thinks" that there is an * in every cell. Any suggestions would be greatly appreciated. Thank you, Roger |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
replacing text in a cell
When you're looking to work on *'s or ?'s, you have to precede them with an
tilde (~). So in Find What, enter ~* -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Roger Converse" wrote in message ... Hello, I have a file with about 5,000 records. A majority of them are fine, but some of them have an * in the cell. This prevents my query from matching up with another file, because there should not be a * in the cell. so for example one file contains this product id: FRK F218026* and the other file contains FRK F218026 I have tried to do a replace, but when I replace the * everything disappears. I next tried to filter on anything that contains an *, but that didn't filter anything. Apparently MS Excel "thinks" that there is an * in every cell. Any suggestions would be greatly appreciated. Thank you, Roger |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
replacing text in a cell
Thank you both for the reply. I tried the ~* method and it worked perfectly.
I will keep that other formula handy. I am sure I can find uses for that as well. Thank you, Roger "RagDyer" wrote: When you're looking to work on *'s or ?'s, you have to precede them with an tilde (~). So in Find What, enter ~* -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Roger Converse" wrote in message ... Hello, I have a file with about 5,000 records. A majority of them are fine, but some of them have an * in the cell. This prevents my query from matching up with another file, because there should not be a * in the cell. so for example one file contains this product id: FRK F218026* and the other file contains FRK F218026 I have tried to do a replace, but when I replace the * everything disappears. I next tried to filter on anything that contains an *, but that didn't filter anything. Apparently MS Excel "thinks" that there is an * in every cell. Any suggestions would be greatly appreciated. Thank you, Roger |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
replacing text in a cell
You're welcome, and thank you for the feed-back.
If you like using formulas, you can try this one: =SUBSTITUTE(A1,"*","") -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Roger Converse" wrote in message ... Thank you both for the reply. I tried the ~* method and it worked perfectly. I will keep that other formula handy. I am sure I can find uses for that as well. Thank you, Roger "RagDyer" wrote: When you're looking to work on *'s or ?'s, you have to precede them with an tilde (~). So in Find What, enter ~* -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Roger Converse" wrote in message ... Hello, I have a file with about 5,000 records. A majority of them are fine, but some of them have an * in the cell. This prevents my query from matching up with another file, because there should not be a * in the cell. so for example one file contains this product id: FRK F218026* and the other file contains FRK F218026 I have tried to do a replace, but when I replace the * everything disappears. I next tried to filter on anything that contains an *, but that didn't filter anything. Apparently MS Excel "thinks" that there is an * in every cell. Any suggestions would be greatly appreciated. Thank you, Roger |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Replacing Text for Template | Excel Worksheet Functions | |||
replacing text in all cells with existing text plus something | Excel Discussion (Misc queries) | |||
Replacing #N/A with other text | Excel Worksheet Functions | |||
Help replacing text with Yes or No | Excel Discussion (Misc queries) | |||
INTRICATE PROBLEM- How to find multiple text,excluding "H", in a multiple range of cells, then replacing 0 with another number in another cell | Excel Worksheet Functions |