Seperating a text path in Excel

[Tawreq]

Hi All,

I have quite a unique issue - I would be very grateful if someone
could help out with this challenge....

I have two columns, Column A contains and acronym, and Column B
contains a corresponding file path. Like below:

Column A
FSSC
GPM SBU
AL ACT
.....

Column B
/_op_sox/Project/Default/ICDocumentation/Controls/LSSB/ITO/GPM/FSSC/
ADM-E-R001-C001.txt

/_op_sox/Project/Default/ICDocumentation/Controls/LSSB/ITO/GPM/GPM SBU/
ADM-I-R003-R001.txt

/_op_sox/Project/Default/ICDocumentation/Controls/LSSB/II/AL/AL ACT/
ALV-A-R001-C001.txt

....


The 9th folder in the file path matches the acronym in column A, but
only 90% of the time. I have around 3000 rows and I need to highlight
which ones don't match the acronym in column A. Basically, what I want
to do, is chop off everything before 9th "\" and everything after the
10th "\" and see if it matches column A - can anyone solve this
challenge?

Thank you so much!

Regards,
Tariq

 

[Guest]

This is an Access Data Base Group, I suggest you post your question in an
Excel Group.

However, In Cell C1 Type

=FIND(A1,B1) (Copy all formulae down to the number of rows you require)

This will return the position of the first character in the B1 String which
matches the string in A1. As example 64 A1 to B1, 64 in A2 to B2 and 62 in
row A3 to B3. (Or

From this, the start position of the matching text you can find the matching
text as follows.

In Cell D1 Type

=IF(ISERROR(C1),"",MID(B1,C1,(LEN(A1)))) (Copy all formulae down to the
number of rows you require)


This will return the matching text in the cell C1 or leave empty if no match
is found.

The fact that you now have either the matching text to cell A1 or a blank
cell should assist you in identifying the matches. Something like
=If(D1=â€â€,True,False) typed into cell E1 will populate either True or False
in Column D

HTH Mike

--
An Engineers Prayer:
At the very end of the day,
when all else fails,
you have tried all
and asked everyone you know,
read the instruction manual.

 

[Nikos Yannacopoulos]

Tariq,

You have posted this to the wrong NG, this one is for MS-Access, not Excel.

Since I bothered though: make a new copy of column B in Column C, and
then go to menu path Data > Text To Columns (with Column C selected);
select option Delimited, click Next, and type the slash in the box next
to the Other ckeck option; complete the Text to Columns wizard (probably
selecting Do Not Import Column for all aother columns except the one you
are interested). Now you have separated the ninth level, as you wanted.
Having gotten so far, just use the next available column (say, D) to
enter this formula in D1:
= EXACT(A1, C1)
and copy all the way down. This will return TRUE for exact matches,
FALSE for non-matches.

HTH,
Nikos