# Solved Find a text string in excel

August 12, 2014 at 00:50:40
Specs: Windows 7
 HI I have a sheet containing 500 rows of data, I have company names in Row A. In Row B I want to find just the first part of the company name. For example:If the company name is "Thomas Graham & Sons" or "John Smith Co" I want to be able to find the "Thomas" and the "John" - is this possible?Many thanks,Gary

See More: Find a text string in excel

August 13, 2014 at 07:25:24
 There is no easy way to cut apart a string in Excel.You will need to use various formulas using, among others, the LEFT(), RIGHT(), and MID() functions.Here are three that should get you started, but are by nomeans all that you will need.If your string is in cell A1 like``` A 1) John Smith Co ```This formula will get you the first string of characters from the Left,up to the first space character: =LEFT(A1,FIND(" ",A1,1))You should get "John"------------------------------------IF there are only two parts to the string of characters,then this formula will get the last string of charactersfrom the rightup to the last spaceIf your string is in cell A1 like``` A 1) John Smith ```=RIGHT(A1,LEN(A1)-FIND(" ",A1))You should get "Smith"------------------------------------------IF there are only three parts to the string of characters,then this formula will get the middle string of charactersfrom the first space up to the last spaceIf your string is in cell A1 like``` A 1) John Smith Co ```=LEFT(RIGHT(A1,LEN(A1)-FIND(" ",A1,1)),FIND(" ",RIGHT(A1,LEN(A1)-FIND(" ",A1,1)),1))You should get "Smith" again.As you can see, things get pretty complicated pretty fast.Not knowing why you need to cut apart your string the onlyother option I can think of would be to use the Text to Columns function and split the whole string into cells and then pick what you need.There may be a Macro option, but my macro skills are just above nill.MIKEhttp://www.skeptic.com/

#1
August 12, 2014 at 04:07:41
 What do you mean by "find"? Are you just trying to locate the row within your list and go to that company name? That can be done by using the Find feature and searching for a partial match.Are you try into extract the company name and put it in another cell?What do you want to happen if there are duplicate first names?Thomas Graham & SonsThomas Graham & Daughters

Report •

#2
August 12, 2014 at 04:13:43
 Yes, apologies, I am trying to find the name, and extract parts of it (e.g First name/middle name and surname etc) and put it in another cell.Duplicates are not an issue. Thanks.

Report •

#3
August 12, 2014 at 09:41:44
 Are you using "Edit > Find" from the top bar?Always pop back and let us know the outcome - thanks

Report •

Related Solutions

#4
August 13, 2014 at 05:20:42
 No i',m not using Edit>Find as I want to use a formula which can be repeated in numerous rows.

Report •

#5
August 13, 2014 at 07:25:24
 There is no easy way to cut apart a string in Excel.You will need to use various formulas using, among others, the LEFT(), RIGHT(), and MID() functions.Here are three that should get you started, but are by nomeans all that you will need.If your string is in cell A1 like``` A 1) John Smith Co ```This formula will get you the first string of characters from the Left,up to the first space character: =LEFT(A1,FIND(" ",A1,1))You should get "John"------------------------------------IF there are only two parts to the string of characters,then this formula will get the last string of charactersfrom the rightup to the last spaceIf your string is in cell A1 like``` A 1) John Smith ```=RIGHT(A1,LEN(A1)-FIND(" ",A1))You should get "Smith"------------------------------------------IF there are only three parts to the string of characters,then this formula will get the middle string of charactersfrom the first space up to the last spaceIf your string is in cell A1 like``` A 1) John Smith Co ```=LEFT(RIGHT(A1,LEN(A1)-FIND(" ",A1,1)),FIND(" ",RIGHT(A1,LEN(A1)-FIND(" ",A1,1)),1))You should get "Smith" again.As you can see, things get pretty complicated pretty fast.Not knowing why you need to cut apart your string the onlyother option I can think of would be to use the Text to Columns function and split the whole string into cells and then pick what you need.There may be a Macro option, but my macro skills are just above nill.MIKEhttp://www.skeptic.com/