MID and FIND Data extraction help

[lee.russell]

Hello,

I have some data that is saved in text and I want to be able to
manipulate it in Excel. The easiest way for me to explain it is to
provide an example. Some of my data is the following:


SERIAL NUM = 06Y911002651 APP CODE = TIM1 COMCODE = 201270600
ART MASTER = 2 SERIES = S3 VINTAGE =
SERIAL NUM = 06Y911000204 APP CODE = 44WW65B COMCODE = 109596015
ART MASTER = 1 SERIES = S5 VINTAGE =

Now what I would like to do is create 3 columns in Excel labeled

Serial Number APP CODE COMCODE
06Y911002651 TIM1-S3 201270600
06Y911000204 44WW65B-S5 109596015


Now I know I can get the Serial number and comcode easily but the APP
CODE is tricky.

I want to take the first part of the code and combine it with the
series and I know I can use

=CONCATENATE(,"-",) to do that

But I am having problems Extracting only the TIM1 and 44WW65B since
they are different lengths. And if I make the length to long using
MID() it will interfere with COMCODE.

So I am getting:

TIM1 COMC-S3
44WW65B-S5

(I am using trim to take out the extra spaces)

Any suggestions?

 

[Toppers]

Assuming text string in a single cell:

Try:

=TRIM(MID(A1,FIND("APP CODE",A1)+11,FIND("COMCODE",A1)-41))

or

=TRIM(MID(A1,40,FIND("COMCODE",A1)-41))

HTH

 

[OssieMac]

Hi,

the following assumes that you do not know the number of spaces etc between
the data but there is always a known title followed by = sign.

Assume full string in Cell A1

In cell B1:-
Find APP CODE:- =FIND("APP CODE",A1,1)

In cell C1:-
Find = sign after APP CODE and add 1:- =FIND("=",A1,B2+1) + 1

In cell D1:-
Find COMCODE and subtract 1:- =FIND("COMCODE",A1) – 1

In cell E1:-
=TRIM(MID(A1,C2,D2-C2))

You can nest the formulas after you get it right but the above is the
principle of extracting the data which is:-

Find the numeric position of the title preceding the required text.

Find the numeric position of = sign prior after the title and add 1.

Find the numeric position of the known title after the = sign and subtract 1.

Use mid function to extract text from after the = sign to before the next
title.

Trim the result to obtain just the data.

Regards,

OssieMac

 

[OssieMac]

Hi again Lee,

I ran out of time when I last answered. I have put a little more work into
it and if you do the following it might save you a lot of hassle.

Set up your worksheet with the following column headers:-

A1: INITIAL STRING
B1: SERIAL NUM
C1: APP CODE
D1: COMCODE
E1: ART MASTER

The headers in B1 to E1 must exactly match the titles in your data because
they are used in the formulas.

The header ART MASTER is required. (Hide the column if you do not want to
see it.)

Put your data into column A starting from A2.

Enter the following formula into cell B2. (Note that it is one line even
though it breaks during this post.)

=TRIM(MID($A2,FIND("=",$A2,FIND(B$1,$A2,1)+1)+1,FIND(C$1,$A2)-1-(FIND("=",$A2,FIND(B$1,$A2,1)+1)+1)))


Copy the formula across to D2

Copy B22 down the page.

You can add additional headers if you want more data extracted and drag the
formula across. The headers must be in the same order as in the data string.
However, it will not extract the last data of the string which would have to
be handled separately.

Regards,

OssieMac