Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
FIND SPECIFIC TEXT AND REPLACE IT
I need to find the word "Void" in one column of data and if it is there (and
it would be in ALL CAPS), then the return needs to be V; if it's not there, the return needs to be I. Plus, would it be possible for the V or the I to replace the original data? I've tried the if function, but I'm not putting in the right criteria obviously... The data is imported from QuickBooks into Excel and contains the check MEMO field. I am trying to find Voided Checks for a daily batch report to send to our bank. This is the only way I've found to capture that data at this point, but I need the column to have only either V or I... Any help would be greatly appreciated! Thanks so much, |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
FIND SPECIFIC TEXT AND REPLACE IT
Have you considered using Edit | Find & Replace?
best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Tree" wrote in message ... I need to find the word "Void" in one column of data and if it is there (and it would be in ALL CAPS), then the return needs to be V; if it's not there, the return needs to be I. Plus, would it be possible for the V or the I to replace the original data? I've tried the if function, but I'm not putting in the right criteria obviously... The data is imported from QuickBooks into Excel and contains the check MEMO field. I am trying to find Voided Checks for a daily batch report to send to our bank. This is the only way I've found to capture that data at this point, but I need the column to have only either V or I... Any help would be greatly appreciated! Thanks so much, |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
FIND SPECIFIC TEXT AND REPLACE IT
Highlight the column of data and do CTRL-H (or Edit | Replace), and
then: Find what: Void Replace with: V click Replace All. Then you could do F5 | Special | Blanks and type I in the first cell and CTRL-Enter. Or you could use an Autofilter on that column, using Custom | Does not Contain | Void, then manually change the first visible cell to I and then copy this down over the rest of the visible cells. Then apply the filter again: Custom | Contains | Void, then manually change the first visible cell to V and copy this down over the other visible cells. Then select All on the filter pull-down. Hope this helps. Pete On Mar 26, 3:38*pm, Tree wrote: I need to find the word "Void" in one column of data and if it is there (and it would be in ALL CAPS), then the return needs to be V; if it's not there, the return *needs to be I. Plus, would it be possible for the V or the I to replace the original data? I've tried the if function, but I'm not putting in the right criteria obviously... The data is imported from QuickBooks into Excel and contains the check MEMO field. I am trying to find Voided Checks for a daily batch report to send to our bank. This is the only way I've found to capture that data at this point, but I need the column to have only either V or I... Any help would be greatly appreciated! Thanks so much, |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
FIND SPECIFIC TEXT AND REPLACE IT
I didn't think that would work as I want it to compare the text and then
return a value based on what it finds... not just replace the text... "Bernard Liengme" wrote: Have you considered using Edit | Find & Replace? best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Tree" wrote in message ... I need to find the word "Void" in one column of data and if it is there (and it would be in ALL CAPS), then the return needs to be V; if it's not there, the return needs to be I. Plus, would it be possible for the V or the I to replace the original data? I've tried the if function, but I'm not putting in the right criteria obviously... The data is imported from QuickBooks into Excel and contains the check MEMO field. I am trying to find Voided Checks for a daily batch report to send to our bank. This is the only way I've found to capture that data at this point, but I need the column to have only either V or I... Any help would be greatly appreciated! Thanks so much, |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
FIND SPECIFIC TEXT AND REPLACE IT
Thanks, Pete, for taking the time to help out! In the interim though, I think
I found a workable formula. I didn't see your reply until after I had done the research. It doesn't do anything for the original cell contents like yours does, but I can just do a macro to delete the original column once I've got my V's and I's.. The formula I used is as follows: =IF(ISNUMBER(SEARCH("void",C2)),"V", "I")... Again, thank you both for taking the time to help! This forum is always very good!!.. "Pete_UK" wrote: Highlight the column of data and do CTRL-H (or Edit | Replace), and then: Find what: Void Replace with: V click Replace All. Then you could do F5 | Special | Blanks and type I in the first cell and CTRL-Enter. Or you could use an Autofilter on that column, using Custom | Does not Contain | Void, then manually change the first visible cell to I and then copy this down over the rest of the visible cells. Then apply the filter again: Custom | Contains | Void, then manually change the first visible cell to V and copy this down over the other visible cells. Then select All on the filter pull-down. Hope this helps. Pete On Mar 26, 3:38 pm, Tree wrote: I need to find the word "Void" in one column of data and if it is there (and it would be in ALL CAPS), then the return needs to be V; if it's not there, the return needs to be I. Plus, would it be possible for the V or the I to replace the original data? I've tried the if function, but I'm not putting in the right criteria obviously... The data is imported from QuickBooks into Excel and contains the check MEMO field. I am trying to find Voided Checks for a daily batch report to send to our bank. This is the only way I've found to capture that data at this point, but I need the column to have only either V or I... Any help would be greatly appreciated! Thanks so much, |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
FIND SPECIFIC TEXT AND REPLACE IT
You're welcome - thanks for feeding back.
Pete On Mar 26, 8:11*pm, Tree wrote: Thanks, Pete, for taking the time to help out! In the interim though, I think I found a workable formula. I didn't see your reply until after I had done the research. It doesn't do anything for the original cell contents like yours does, but I can just do a macro to delete the original column once I've got my V's and I's.. The formula I used is as follows: =IF(ISNUMBER(SEARCH("void",C2)),"V", "I")... Again, thank you both for taking the time to help! This forum is always very good!!.. "Pete_UK" wrote: Highlight the column of data and do CTRL-H (or Edit | Replace), and then: Find what: * * * * Void Replace with: * * V click Replace All. Then you could do F5 | Special | Blanks and type I in the first cell and CTRL-Enter. Or you could use an Autofilter on that column, using Custom | Does not Contain | Void, then manually change the first visible cell to I and then copy this down over the rest of the visible cells. Then apply the filter again: Custom | Contains | Void, then manually change the first visible cell to V and copy this down over the other visible cells. Then select All on the filter pull-down. Hope this helps. Pete On Mar 26, 3:38 pm, Tree wrote: I need to find the word "Void" in one column of data and if it is there (and it would be in ALL CAPS), then the return needs to be V; if it's not there, the return *needs to be I. Plus, would it be possible for the V or the I to replace the original data? I've tried the if function, but I'm not putting in the right criteria obviously... The data is imported from QuickBooks into Excel and contains the check MEMO field. I am trying to find Voided Checks for a daily batch report to send to our bank. This is the only way I've found to capture that data at this point, but I need the column to have only either V or I... Any help would be greatly appreciated! Thanks so much,- Hide quoted text - - Show quoted text - |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Replace can't find text | Excel Discussion (Misc queries) | |||
find text in cell and replace it with part of the text in that ce. | Excel Discussion (Misc queries) | |||
Find & Replace in Text Box | Excel Discussion (Misc queries) | |||
can you find specific text in a string ignoring any other text | Excel Discussion (Misc queries) | |||
Find/Replace Text In Cells | Excel Discussion (Misc queries) |