| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Extract specific text
Yeah that did the trick, thanks so much :)
"Bernard Liengme" wrote: If you are getting #NAME! error you most likely have placed the function in the wrong place Use Tools | Macro | VBA editor Make sure your workbook is select in the Project window (left hand panel) Use Insert | Module Past my function in the module window Still not working? Send me a private message (remove TRUENORTH) best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "NunRacer" wrote in message ... I've tried the first more simple formulas and yes yours works better because occasionaly i do have names with no preceding text. Also another problem is that some names have 2 first names which really throws things off. I already have master list of names about 400 or so that i do a vlookup and where the final result is the person first name with the last 3 letters of their last name eg. Joe Blo and their position in the company. As of yet i havnt been able to get the macro to work, comes out as #name? "Rick Rothstein (MVP - VB)" wrote: I'm not trying to divert you from Bernard's solution, but given you said you were sort of new to Excel, I thought you might have missed the point behind the formulas Harlan and I have been writing back and forth about, particularly the last one by each of us. Each of them can retrieve the names you are looking for without the need for a separately maintained "master list" (that is, you do not need a Mylist like from Bernard's posting). Just put either of our last formulas in A1 and then copy them down as needed. They will retrieve, and re-order, whatever names are contained inside the text in column A, as long as those names are separated by a comma followed by a single space... and they will do it automatically, without needing a separately maintained list to check against. By the way, the only difference between Harlan's and my last posted formulas has to do with where the names can occur within the text... my formula (a modification of Harlan's by the way) will allow the names to be at the beginning of the text whereas Harlan's requires some non-name text to precede the names. Rick "NunRacer" wrote in message ... I think this is what i'm looking for now to make it work :) I'm sort of new to excel. Thanks everyone for the great responses. "Bernard Liengme" wrote: Somewhere in my workbook (it matter not where) I have a range called Mylist. I contains the names Blow, Joe Liengme, Bernard Smith, Anne Taylor, George but, of course, it could be longer. In A1 and A2 I have your data 3-PKR11500 TGDL1 TAYLOR, GEORGE 15:00 - 23:00 x 3-SP12300 TGDS2 BLOW, JOE 23:00 - 7:00 x In B1 and B2 I call a UDF with =getname(A1,mylist) The UDF is: Function getname(mycell, mylist) mycount = mylist.Count getname = "not found" For j = 1 To mycount testname = UCase(mylist(j).Value) whatname = mycell.Value MyPos = InStr(whatname, testname) If MyPos < 0 Then getname = mylist(j) Exit For End If Next End Function The formulas return the correct names from MyList Need help with VBA? See David McRitchie's site on "getting started" with VBA http://www.mvps.org/dmcritchie/excel/getstarted.htm best wishes -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "NunRacer" wrote in message ... I've tried searching for some solutions to my problem but havnt found anything that really helps me. I want to extract a persons name (the name is mixed up with other useless info, and doesnt follow the same format) from a cell that matches the names i have in a Database. so example would be 3-PKR11500 TGDL1 TAYLOR, GEORGE 15:00 - 23:00 x 3-SP12300 TGDS2 BLOW, JOE 23:00 - 7:00 x So i need a formula to pull the name out by comparing it to the existing names in the database. The end result needs to be just their name. Thank You. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Extract specific value using SEARCH FORMULA | Excel Worksheet Functions | |||
| Extract value from Specific Cell | Excel Worksheet Functions | |||
| Formula to extract a specific word from text string | Excel Worksheet Functions | |||
| Extract specific value from a long text string | Excel Worksheet Functions | |||
| Extract Specific Text | Excel Worksheet Functions | |||
Hybrid Mode
