Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 41
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 4,393
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 41
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 41
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 8,856
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Replace can't find text dac Excel Discussion (Misc queries) 1 October 26th 07 10:26 PM
find text in cell and replace it with part of the text in that ce. jules Excel Discussion (Misc queries) 3 May 3rd 07 10:18 AM
Find & Replace in Text Box Ed Excel Discussion (Misc queries) 10 March 28th 07 03:03 AM
can you find specific text in a string ignoring any other text chriscp Excel Discussion (Misc queries) 1 September 18th 05 09:54 PM
Find/Replace Text In Cells Rob Excel Discussion (Misc queries) 1 April 11th 05 04:01 AM


All times are GMT +1. The time now is 04:51 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"