Hi there, if I have a text string in cell A1, what code can I use in B1 that will extract the text from A1 before the first number or bracket?
Example:
Sebs Sensei (IRE) 8/1 would be - Sebs Sensei
Ermine Ruby 4/1 would be - Ermine Ruby
Hi there, if I have a text string in cell A1, what code can I use in B1 that will extract the text from A1 before the first number or bracket?
Example:
Sebs Sensei (IRE) 8/1 would be - Sebs Sensei
Ermine Ruby 4/1 would be - Ermine Ruby
This may be too simplistic, but if it's always the first two words as a name:
=TRIM(LEFT(SUBSTITUTE(A1, " ", REPT(" ", 50)), 100))
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
for the second question
=LEFT(A2;search({1,2,3,4,5,6,7,8,9},A2,1)-3)
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
try this array entered formula
=IFERROR(LEFT(A1,MIN(IFERROR(SEARCH(CHAR({40,48,49,50,51,52,53,54,55,56,57}),A1),""))-2),"")
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.
Row\Col A B 1Sebs Sensei (IRE) 8/1 Sebs Sensei 2Ermine Ruby 4/1 Ermine Ruby 3Allen John Flavin (FRA) 12/5 Allen John Flavin 4Buffy Hoggatt 7/23 Buffy Hoggatt 5Roxann L Romberg 5/6 Roxann L Romberg 6Klara Grund (RU) 6/29 Klara Grund
Last edited by AlKey; 10-22-2014 at 07:16 PM.
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks