+ Reply to Thread
Results 1 to 7 of 7

Find and Delete Phrases

  1. #1
    Registered User
    Join Date
    10-29-2021
    Location
    USA
    MS-Off Ver
    Pro Plus 2016
    Posts
    35

    Find and Delete Phrases

    Hello everyone. What would be the easiest way to find the below phrases in columns C (First Name), D (Middle Name), E (Last Name) and delete them? For example suppose along those three columns I had:
    Column C - John
    Column D - J.
    Column E - Doe AIF, CFA(R)

    But I needed it to be:
    Column C - John
    Column D - J
    Column E - Doe

    Is there a single formula that can do this? Note the very last one is a double space:

    ,
    .
    (
    )
    %
    &
    (HOS)
    (R)
    AIF
    CFA
    CFP
    CHFC
    CLU
    CPA
    PFS
    Attached Files Attached Files
    Last edited by mightymo77; 01-20-2022 at 10:18 AM.

  2. #2
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,500

    Re: Find and Delete Phrases

    Maybe you can use UDF like this:

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    A
    B
    C
    1
    (HOS) ()John J. Doe AIF, CFA(R) John J Doe
    2
    (R) =MyNewWord(A1:A16,B1)
    3
    AIF
    4
    CFA
    5
    CFP
    6
    CHFC
    7
    CLU
    8
    CPA
    9
    PFS
    10
    11
    ,
    12
    .
    13
    (
    14
    )
    15
    %
    16
    &


    or through columns. But loop through 3 columns (I do not know how many rows you expect to check) and search in values 15 strings to replace take a while.

    Please Login or Register  to view this content.
    or somebody find something faster.
    Happy with my answer * Add Reputation.
    If You are happy with solution, please use Thread tools and mark thread as SOLVED.

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,847

    Re: Find and Delete Phrases

    or...


    A picture is worth 1,000 words. An Excel sheet is worth 1,000 non-editable pictures.

    Please read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet, with representative samples, an explanation and some expected results.
    Glenn



  4. #4
    Registered User
    Join Date
    10-29-2021
    Location
    USA
    MS-Off Ver
    Pro Plus 2016
    Posts
    35

    Re: Find and Delete Phrases

    Thank you very much KOKOSEK. I edited my first post to be more clear. The columns C, D, E are actually three columns of First, Middle, Last Name, it comes this way when exported from the database. My goal is to find those phrases listed and delete them to clean up the names.

  5. #5
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... based in Ireland... but I could be anywhere in the world
    MS-Off Ver
    Office 365 for PC.
    Posts
    34,847

    Re: Find and Delete Phrases

    Hi. Provide SEVERAL representative examples (not just one) on an Excel sheet please!!!

  6. #6
    Registered User
    Join Date
    10-29-2021
    Location
    USA
    MS-Off Ver
    Pro Plus 2016
    Posts
    35

    Re: Find and Delete Phrases

    OK I have attached an example on my first post above.
    Last edited by mightymo77; 01-20-2022 at 12:26 PM.

  7. #7
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,500

    Re: Find and Delete Phrases

    Quote Originally Posted by mightymo77 View Post
    I edited my first post to be more clear. The columns C, D, E are actually three columns of First, Middle, Last Name, it comes this way when exported from the database. My goal is to find those phrases listed and delete them to clean up the names.
    It does not change anything.
    You can use UDF somewhere (other sheet called 'ClearData' or other columns to get clear data).
    Or looping through C/D/E and check. Of course can be auto declared as Range("C1:E" & lastRow).
    But as I said simple loop takes a while if you got lots of rows to check.

    EDIT: Change this line into
    Please Login or Register  to view this content.
    Last edited by KOKOSEK; 01-20-2022 at 10:26 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] find a text in a range, delete it and delete the value in cell to its left
    By snkhan in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-12-2020, 07:18 PM
  2. Replies: 0
    Last Post: 03-06-2017, 06:58 PM
  3. index(match) and find phrases
    By dgrayexcel in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 01-29-2016, 06:41 PM
  4. Find Phrases and copy text in between
    By Cbaffoni87 in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 04-07-2015, 03:45 PM
  5. [SOLVED] Can I find duplicate phrases (not just occurrences of single words)?
    By michigandrea in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 04-17-2014, 09:06 AM
  6. Want to delete every worksheet that *doesn't* contain one of several phrases
    By Televangelist in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 07-08-2011, 11:29 AM
  7. Compare lists of phrases in two workbooks and import only missing phrases
    By Fireblejd in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-10-2011, 06:59 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

X vBulletin 4.1.8 Debug Information

  • Page Generation 0.07513 seconds
  • Memory Usage 9,131KB
  • Queries Executed 16 (?)
More Information
Template Usage (36):
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_global_above_footer
  • (1)ad_global_below_navbar
  • (1)ad_global_header1
  • (1)ad_global_header2
  • (1)ad_navbar_below
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)ad_thread_first_post_content
  • (1)ad_thread_last_post_content
  • (2)bbcode_code
  • (1)bbcode_quote
  • (1)footer
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)headinclude_bottom
  • (7)memberaction_dropdown
  • (1)navbar
  • (4)navbar_link
  • (4)navbar_noticebit
  • (6)option
  • (1)postbit_attachment
  • (7)postbit_legacy
  • (7)postbit_onlinestatus
  • (7)postbit_wrapper
  • (4)showthread_bookmarksite
  • (7)showthread_similarthreadbit
  • (1)showthread_similarthreads
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper
  • (1)vbseo_linkbackmenu_entry 

Phrase Groups Available (6):
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files (40):
  • ./vbseo.php
  • ./env.php
  • ./vbseo/includes/functions_vbseo.php
  • ./vbseo/includes/functions_vbseo_pre.php
  • ./vbseo/includes/functions_vbseo_url.php
  • ./vbseo/includes/functions_vbseo_createurl.php
  • ./vbseo/includes/functions_vbseo_db.php
  • ./vbseo/includes/functions_vbseo_vb.php
  • ./vbseo/includes/functions_vbseo_seo.php
  • ./vbseo/includes/functions_vbseo_misc.php
  • ./vbseo/includes/functions_vbseo_crr.php
  • ./vbseo/includes/functions_vbseo_cache.php
  • ./vbseo/includes/functions_vbseo_hook.php
  • ./vbseo/includes/functions_vbseo_startup.php
  • ./includes/config.php
  • ./showthread.php
  • ./global.php
  • ./includes/class_bootstrap.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/functions.php
  • ./includes/class_friendly_url.php
  • ./includes/class_hook.php
  • ./includes/functions_cforum.php
  • ./includes/functions_facebook.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/class_bootstrap_framework.php
  • ./vb/vb.php
  • ./vb/phrase.php
  • ./packages/vbattach/attach.php
  • ./vb/types.php
  • ./vb/cache.php
  • ./vb/cache/db.php
  • ./vb/cache/observer/db.php
  • ./vb/cache/observer.php
  • ./includes/functions_notice.php
  • ./packages/vbforum/bbcodehelper/table.php 

Hooks Called (49):
  • init_startup
  • friendlyurl_resolve_class
  • database_pre_fetch_array
  • database_post_fetch_array
  • global_bootstrap_init_start
  • global_bootstrap_init_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • global_state_check
  • global_bootstrap_complete
  • global_start
  • style_fetch
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • friendlyurl_redirect_canonical
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • fetch_musername
  • cache_templates
  • template_register_var
  • parse_templates
  • notices_check_start
  • notices_noticebit
  • process_templates_complete
  • reputation_image
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_attachment
  • postbit_display_complete
  • memberaction_dropdown
  • tag_fetchbit_complete
  • showthread_similarthread_query
  • showthread_similarthreadbit
  • forumrules
  • showthread_bookmarkbit
  • navbits
  • navbits_complete
  • showthread_complete


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1