data extraction-Source Data

[benj]

Hi to all...
my problem is that i have a column of data called 'Source Data'. It can be holding just one sentence or a few sentences. The problem comes when i have to extract names from this column.

for example row 1 column 'Source Data'
.......this is because Teller ABC DEF is ......
where ABC DEF is a name of a person

for example row 20 column 'Source Data'
.......this is because Teller AB CCC DDD is ......
where AB CCC DDD is a name of a person

I have to go through each row of data and extract the name that succeed the word "Teller". So how am I able to determine that length of the name? or is there any method that I can extract the name by taking the word after the space of the word "Teller" and end after encountering 2 spaces?
which in this case, Teller_ABC_DEF_ will return ABC_DEF since it meets a space after the word "Teller" and 2 spaces after it will be ABC_(1st space)DEF_(2nd space).

I don't know if i'm being clear enough here. any one can offer any advice?

[Domenic]

Provided that the format is consistent, that is, the name is always preceded by the word 'Teller' and followed by the word 'is', try the following...

=LEFT(REPLACE(A1,1,FIND("Teller",A1)+6,""),FIND("is",REPLACE(A1,1,FIND("Teller",A1)+6,""))-2)

Hope this helps!

Hi to all...
my problem is that i have a column of data called 'Source Data'. It can be holding just one sentence or a few sentences. The problem comes when i have to extract names from this column.

for example row 1 column 'Source Data'
.......this is because Teller ABC DEF is ......
where ABC DEF is a name of a person

for example row 20 column 'Source Data'
.......this is because Teller AB CCC DDD is ......
where AB CCC DDD is a name of a person

I have to go through each row of data and extract the name that succeed the word "Teller". So how am I able to determine that length of the name? or is there any method that I can extract the name by taking the word after the space of the word "Teller" and end after encountering 2 spaces?
which in this case, Teller_ABC_DEF_ will return ABC_DEF since it meets a space after the word "Teller" and 2 spaces after it will be ABC_(1st space)DEF_(2nd space).

I don't know if i'm being clear enough here. any one can offer any advice?

[benj]

i'm sorry.. the format is not always consisitent. as in it might not always be the case where the name is followed by 'is'. as such is there any way ard it?

also how can i write this in vba format?

[Domenic]

.. the format is not always consisitent...

I had a feeling that would be the case. Unfortunately, I don't have any other suggestions. With the format being inconsistent, a solution may prove difficult.

Cheers!