Extracting strings of text from Columns of data(text)

[welshmagic69]

Hi,

Can any help me please to extract strings of text from Columns of data(text) ?

What I have is a column of data(text) which contains amongst all the text three strings of text in ever cell in the column which I require copying into three adjoining cells

The data I require is :-

(a) The persons name which is always after the word ‘Requester’ e.g. Requester Steve Robinson

(b) Their office location which is directly after the persons name and is in brackets e.g. (Newcastle User)

(c) The Approving persons name which is preceded by ‘Approved by’ e.g. Approved by Christine Hunting

See examples 1 & 2 below

Example 1

CR0/CRZ3651 Requestor Steve Robinson (Newcastle User) Tel: 01234 798157 Approved by Christine Hunting

Please install and configure 2 Ultra 2s (typhoon and lancaster) for use as ARTE workstations. These workstations require Solaris 2.5.1 plus the same patches as before

Example 2

CR0/CRZ3118 Requestor Doug Cunningham (Newport User) Tel: 0114 9881480 Approved by John Smithers

Please provide support to set up Cisco 2691 Router and PIX-506E Firewall to enable external connection of a remote terminal for project work.





As you will appreciate the text in the cells is of non standard lenght and the three pieces of information can be located virtually any where in the text

Any assistance on this matter would be grateful appreciated

Many thanks


Rob aka welshmagic69

[NBVC]

Assuming Original String is in A1:

Requestor:

Please Login or Register  to view this content.

Office Location:

Please Login or Register  to view this content.

Approved By:

If this occurs always at the end:

Please Login or Register  to view this content.

If this can occur anywhere within string:

Please Login or Register  to view this content.

[welshmagic69]

NBVC

Many thanks for your prompt reply it is very much appreciate - Obtaining the persons name and office location works brilliant, however, when I try to use the code to locate 'Approved by' using your code that assumes that 'Approved by' can be anyway in the text i.e.

=IF(ISTEXT(MID(A1,SEARCH("Approved By",A1)+12,LEN(A1))),MID(A1,SEARCH("Approved By",A1)+12,LEN(A1)),MID(A1,SEARCH("Approved By",A1)+12,FIND(" ",SUBSTITUTE(MID(A1,SEARCH("Approved By",A1)+12,LEN(A1))," ","#",1))))


I get the following results (using the data from my two examples)

"Christine Hunting Please install and configure 2 Ultra 2s (typhoon and lancaster) for use as ARTE workstations. These workstations require Solaris 2.5.1 plus the same patches as before"

"John Smithers Please provide support to set up Cisco 2691 Router and PIX-506E Firewall to enable external connection of a remote terminal for project work.
."

In addition after the last character of their names there are two little squares being display before the rest of the text e.g. Christine Hunting [] [] Christine Hunting

Any help would be invaluable

Regards

Rob aka welshmagic69

[NBVC]

Does this work?

Please Login or Register  to view this content.

[welshmagic69]

NBVC

Hi still a few problems with the last code (in column D) of my attached example, in that D1 is ok, D2 has John Smithers [][]Please, D3 is ok, D4 has "Thomas Guy [] Please" and D5 is ok.

I think the problem lies where there is a Ctrl +enter in the Column A

Any ideas

Rob aka welshmagic69

[NBVC]

I don't see any attachments. Please try attaching your sample sheet again.

[welshmagic69]

Sorry

Example File Attached

Rob aka welshmagic69

[NBVC]

Thank you.

See attached.

I added a hidden column, column D to get rid of the carriage returns....

Formula used: =SUBSTITUTE(A1,CHAR(10)," ")

Then I used my previously suggested formula and replaced references to column A with references to column D.

Hope this does it.

[welshmagic69]

NBVC

It Works !!!


Many Thanks


Rob aka welshmagic69

[NBVC]

Great!

You're very welcome!