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

Report •

✔ Best Answer
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 no
means 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 characters
from the right
up to the last space

If 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 characters
from the first space
up to the last space

If 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 only
other 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.

MIKE

http://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 & Sons
Thomas Graham & Daughters

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


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
✔ Best Answer
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 no
means 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 characters
from the right
up to the last space

If 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 characters
from the first space
up to the last space

If 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 only
other 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.

MIKE

http://www.skeptic.com/


Report •

Ask Question