Excel 2007 : Looking for help with finding words of different lengths in a simular sentence.

**ZoeJade** · 02-12-2012, 09:22 AM

can anybody help me with this for example...

She has a friend called Amelia she is 14.
She has a friend called Shannon she is 14.
She has a friend called Faye she is 14.
She has a friend called Jasmin she is 14.
She has a friend called Fern she is 14.
She has a friend called Heidi she is 14.
She has a friend called Sophie she is 14.
She has a friend called Olivia she is 14.
She has a friend called Charlotte she is 14.
She has a friend called Emma she is 14.

How would i extract just the names from these sentences without going through them all individually? i have tried left,right and middle functions but they dont work because i still have to go through each one after to correct each formular becuase of the different number of characters in each word.

please could someone help me?

**bebo021999** · 02-12-2012, 09:34 AM

Assuming your data is in A column from A1
Try this in B1:

Please Login or Register  to view this content.

Copy down

**ZoeJade** · 02-12-2012, 10:14 AM

Thankyou this has helped put how would i do this if it was for example..

She has a friend called Amelia she is 14.
She has a friend called Shannon she is 14.
He has a friend called Faye she is 14.
They has a friend called Jasmin she is 14.
He has a friend called Fern she is 14.
We has a friend called Heidi she is 14.
We has a friend called Sophie she is 14.
You have a friend called Olivia she is 14.
She has a friend called Charlotte she is 14.
He has a friend called Emma she is 14.

How would i change the formular to suit this?

**ZoeJade** · 02-12-2012, 10:23 AM

Thankyou this has helped put how would i do this if it was for example..

She has a friend called Amelia she is 14.
She has a friend called Shannon she is 14.
He has a friend called Faye she is 14.
They has a friend called Jasmin she is 14.
He has a friend called Fern she is 14.
We has a friend called Heidi she is 14.
We has a friend called Sophie she is 14.
You have a friend called Olivia she is 14.
She has a friend called Charlotte she is 14.
He has a friend called Emma she is 14.

How would i change the formular to suit this?

**bebo021999** · 02-12-2012, 10:27 AM

Try this:

Please Login or Register  to view this content.

**ZoeJade** · 02-12-2012, 10:44 AM

They both work for the examples ive gave but im not sure now how i would change the formulars to now help me in my work? please could you help?

**bebo021999** · 02-12-2012, 11:00 AM

It appears that all sentences have "called" embedded.

Example: She has a friend called Amelia she is 14

+ Searching "called": SEARCH("called",A1) ----> 18

+ Achieving string after "called": MID(A1,SEARCH("called",A1)+7,256)
----> "Amelia she is 14"

+ Searching first "space" in string "Amelia she is 14": SEARCH(" ",MID(A1,SEARCH("called",A1)+7,256)) ---> 7

+ Achieving string before first "space" in string "Amelia she is 14": =LEFT(MID(A1,SEARCH("called",A1)+7,256),SEARCH(" ",MID(A1,SEARCH("called",A1)+7,256)))
-----> "Amelia"

**ZoeJade** · 02-12-2012, 11:02 AM

say like i had something a little more difficult like this with letters and symbols how would i manage this?

<option value="Amelia">Amelia</option>
<option value="Shannon">Shannon</option>
<option value="Faye">Faye</option>
<option value="Jasmin">Jasmin</option>
<option value="Fern">Fern</option>
<option value="Heidi">Heidi</option>
<option value="Sophie">Sophie</option>
<option value="Olivia">Olivia</option>
<option value="Charlotte">Charlotte</option>
<option value="Emma">Emma</option>

**ZoeJade** · 02-12-2012, 11:07 AM

<option value="AMELIS">Amelia</option>
<option value="SHANNON">Shannon</option>
<option value="FAYE">Faye</option>
<option value="JASMIN">Jasmin</option>
<option value="FERN">Fern</option>
<option value="HEIDI">Heidi</option>
<option value="SOPHIE">Sophie</option>
<option value="OLIVIA">Olivia</option>
<option value="CHARLOTTE">Charlotte</option>
<option value="EMMA">Emma</option>

How would i find the names in capitals? please could you help me?

**bebo021999** · 02-12-2012, 11:09 AM

Try to practise with my suggestion: Name is between first { " } and next { " }

**ZoeJade** · 02-12-2012, 11:14 AM

Thankyou for your help i have tried but when i try to apply the formulars and help you have give me but with the infomation i have before the word i need i get an error message..

do you know why this is?

**bebo021999** · 02-12-2012, 11:21 AM

What is your formula so far for query in #9?

**bebo021999** · 02-12-2012, 11:23 AM

Try to catch it and you will find it easy for any raising query like this.

**ZoeJade** · 02-12-2012, 11:28 AM

i tried different ones but none seem to work

...

most of my work is set up like this...

<option value="AMELIA">Amelia</option>
<option value="SHANNON">Shannon</option>
<option value="FAYE">Faye</option>
<option value="JASMIN">Jasmin</option>
<option value="FERN">Fern</option>
<option value="HEIDI">Heidi</option>
<option value="SOPHIE">Sophie</option>
<option value="OLIVIA">Olivia</option>
<option value="CHARLOTTE">Charlotte</option>
<option value="EMMA">Emma</option>

so finiding a way to pick out the word in capitals would be very helpful.

is they a way i could ask for the word inbetween {"} and {"}? do you know how i could do this?

**bebo021999** · 02-12-2012, 12:35 PM

Try this:

Please Login or Register  to view this content.

**ZoeJade** · 02-12-2012, 12:53 PM

Thankyou this had solved my problem and has worked

but please could you explain how and why this works and how you manage to come up with this formular?

**bebo021999** · 02-12-2012, 01:17 PM

(") is one of special characters which help Excel to identify the TEXT. i.e "ZoeJade"
In case of special character like (",*,?,...) itself is part of TEXT, i.e "Zoe"Jade", or "abc*",
we cann't build formula in normal way like: SUMIF(A1:A10,"*",B1:B10) or COUNTIF(A1:A10,""")
The correct is:

=SUMIF(A1:A10,"~*",B1:B10)
or
=SUMIF(A1:A10,""*",B1:B10)

=COUNTIF(A1:A10,"""")
or
=COUNTIF(A1:A10,"~"")

**ZoeJade** · 02-13-2012, 09:45 AM

thankyou this has solved my problem

Excel 2007 : Looking for help with finding words of different lengths in a simular sentence.

LinkBack

Thread Tools

Rate This Thread

Display

Looking for help with finding words of different lengths in a simular sentence.

Re: Looking for help with finding words of different lengths in a simular sentence.

Re: Looking for help with finding words of different lengths in a simular sentence.

Re: Looking for help with finding words of different lengths in a simular sentence.

Re: Looking for help with finding words of different lengths in a simular sentence.

Re: Looking for help with finding words of different lengths in a simular sentence.

Re: Looking for help with finding words of different lengths in a simular sentence.

Re: Looking for help with finding words of different lengths in a simular sentence.

Re: Looking for help with finding words of different lengths in a simular sentence.

Re: Looking for help with finding words of different lengths in a simular sentence.

Re: Looking for help with finding words of different lengths in a simular sentence.

Re: Looking for help with finding words of different lengths in a simular sentence.

Re: Looking for help with finding words of different lengths in a simular sentence.

Re: Looking for help with finding words of different lengths in a simular sentence.

Re: Looking for help with finding words of different lengths in a simular sentence.

Re: Looking for help with finding words of different lengths in a simular sentence.

Re: Looking for help with finding words of different lengths in a simular sentence.

Re: Looking for help with finding words of different lengths in a simular sentence.

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions