+ Reply to Thread
Results 1 to 6 of 6

pull out desired CONSONANT

  1. #1
    Forum Contributor
    Join Date
    01-04-2014
    Location
    East Africa
    MS-Off Ver
    MS OFFICE 2019 PRO
    Posts
    3,616

    pull out desired CONSONANT

    i Have the following data on B and C

    DNL DANELAW
    DNL ADNEXAL

    I want formula on a1 drag down to give me result as follows

    W
    X

  2. #2
    Forum Contributor
    Join Date
    01-04-2014
    Location
    East Africa
    MS-Off Ver
    MS OFFICE 2019 PRO
    Posts
    3,616

    Re: pull out desired CONSONANT

    I should work any vowel combination like this too

    DNL BALADIN
    DNL PALADIN

    Result will be

    B
    P

  3. #3
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,363

    Re: pull out desired CONSONANT

    Need more examples please in W/book. And in future a better explanation of what is required !
    Last edited by JohnTopley; 07-15-2021 at 02:28 PM.

  4. #4
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,363

    Re: pull out desired CONSONANT

    This

    =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE($C1,MID(B1,1,1),""),MID($B1,2,1),""),MID($B1,3,1),""),"A",""),"E",""),"I",""),"O",""),"U","")

  5. #5
    Forum Contributor
    Join Date
    01-04-2014
    Location
    East Africa
    MS-Off Ver
    MS OFFICE 2019 PRO
    Posts
    3,616

    Re: pull out desired CONSONANT

    Thank you I am sorted so quick , Thank you so much again

  6. #6
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 365
    Posts
    2,420

    Re: pull out desired CONSONANT

    Here is a more compact array-entered** formula for you to consider that will also work...

    =MID(C1,MAX(IF(ISERROR((FIND(MID(C1,ROW($1:$99),1),B1&"AEIOU"))),ROW($1:$99),0)),1)

    **Commit this formula using CTRL+SHIFT+ENTER and not just Enter by itself

+ 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] Formula to Remove Duplicates and Pull Unique Values into Desired Column
    By bjnockle in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 11-11-2020, 10:33 AM
  2. Formula to look across column and pull values into desired table.
    By bjnockle in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 02-25-2020, 10:21 PM
  3. [SOLVED] Macro to pull same consonant words
    By makinmomb in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 11-05-2017, 10:37 AM
  4. Macro to pull same consonant words
    By makinmomb in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 11-02-2017, 06:18 AM
  5. [SOLVED] words with repeat consonant
    By makinmomb in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 04-06-2017, 02:38 AM
  6. pull data from two sheet and paste into master sheet with desired column's only
    By breadwinner in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-13-2014, 03:39 AM
  7. [SOLVED] Splitting word list based on syllabic structure, CVC (consonant/vowel/consonant) etc
    By Jay Bee in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 10-23-2013, 10:36 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1