Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 08-17-2009, 09:55 AM
themangoagent themangoagent is offline IF statement that adds text to a cell Windows XP IF statement that adds text to a cell Office 2003
Novice
IF statement that adds text to a cell
 
Join Date: Jul 2009
Posts: 9
themangoagent is on a distinguished road
Default IF statement that adds text to a cell

So I am using web query to import data from a web source. These are all either doctors, or facilities. The doctors's names are all imported with last name then first name, ex: SMITH, JOHN. Facilities are not the problem. I want to create an IF statement that will recognize the comma between the last and first name, and then if there is one (because there's only a comma in the doctors's names, not the facilities) to add DR. before the name.
I am then mail merging this to a letter. So maybe it (the word "DR." )doesn't necessarily need to be in the same cell as the doctors's name.

Thanks for checking this out!



Maybe something like this: =IF(ISNUMBER(SEARCH("*dog*",A1)),"dog","")

Last edited by themangoagent; 08-17-2009 at 09:59 AM. Reason: Found an IF statement that might work?
Reply With Quote
  #2  
Old 08-19-2009, 09:22 PM
MikeWM MikeWM is offline IF statement that adds text to a cell Windows XP IF statement that adds text to a cell Office 2003
Novice
 
Join Date: Aug 2009
Posts: 4
MikeWM is on a distinguished road
Default

Try this.

=IF(FIND(",",A1)>1,"Dr. " & LEFT(A1,((FIND(",",A1)-1))))

It goes a step further and extracts the entire surname.

Otherwise, just use the find the command to identify the comma. Find returns the number of characters up to the search term (,). So if the result is > 0 then, you have found a doctor.

Cheers.
Reply With Quote
  #3  
Old 08-24-2009, 08:41 AM
themangoagent themangoagent is offline IF statement that adds text to a cell Windows XP IF statement that adds text to a cell Office 2003
Novice
IF statement that adds text to a cell
 
Join Date: Jul 2009
Posts: 9
themangoagent is on a distinguished road
Default

Thanks MikeWM! It's working quite well, except that Excel really doesn't like it if there isn't a comma. It returns an error: #VALUE!
Which is fine, but is there any way for it to copy the cell to the new column if there isn't any comma?
Example: Smith, John copies to the column as Dr. Smith, while Disneyland returns the error. Anyway to just copy Disneyland straight to the cell if there's no comma in the cell?
Thanks for your help!
Reply With Quote
  #4  
Old 08-24-2009, 04:05 PM
MikeWM MikeWM is offline IF statement that adds text to a cell Windows XP IF statement that adds text to a cell Office 2003
Novice
 
Join Date: Aug 2009
Posts: 4
MikeWM is on a distinguished road
Default

Sure, just insert an error check:

=IF(ISERR(FIND(",",A1)),A1,"Dr. "&LEFT(A1,((FIND(",",A1)-1))))


Cheers.
Reply With Quote
  #5  
Old 08-26-2009, 08:07 AM
themangoagent themangoagent is offline IF statement that adds text to a cell Windows XP IF statement that adds text to a cell Office 2003
Novice
IF statement that adds text to a cell
 
Join Date: Jul 2009
Posts: 9
themangoagent is on a distinguished road
Default

Thanks a lot for your help, MikeWM!
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
IF statement that adds text to a cell Vlookup and If statement problem bunnygum Excel 1 03-24-2009 05:10 AM
Auto-populate an MS Word table cell with text from a diff cell? dreamrthts Word Tables 0 03-20-2009 01:49 PM
Templates: automatic text generation from Rich Text content control Chickenmunga Word 0 10-01-2008 11:16 AM
Help - Word adds secret code? Copy to notepad? andytaylor2006 Word 0 12-15-2006 11:00 PM
IF statement that adds text to a cell paste formated text from Word to only one Excel cell heron Word 1 12-06-2005 02:37 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 03:42 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2021, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2021 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft