Excel String Search

[seekerfaith]

I have a excel document report based one of the columns has concatenated data together with '/' it appears that most of the data is in the third position i.e. /ABC/ABC/ABC/ABC/12034.

I've seen the use of mid/search but this has not worked with the third time '/' appears. All sections of the concatenated string can vary in size so I have not been able to use the formula with x position.

 

[Snakehips]

seeker faith
Welcome to MrExcel.

Not 100% clear on what you want.

Do you mean this?

Excel Workbook
	A	B
1	/abc/cde/My Text? /vwx/1234	My Text?
Sheet13

Hope that helps.

 

[seekerfaith]

Hi Tony,

Sorry about the lack of clarity on the post, you have exactly what I was looking for. Could you explain this I've looked at the individual formulas but could do with a break down so I can understand this and be able to use it if I need to look at any other positions.

 

[wideboydixon]

It's an ingenious solution

Starting from the inside:

SUBSTITUTE(A1,"/",REPT(" ",999))
This will replace all "/" characters in the cell contents with 999 spaces.

MID(...,2999,999)
This will take the contents from character 2999 and 999 characters long. This encapsulates the characters between the third and fourth "/" in the original string even though they are surrounded by spaces

TRIM(...)
This removes leading and trailing spaces from the result which just extracts the "My Text?" piece.

There is small downside to the formula in that from "/abc/cde/My Text? /vwx/1234" it will extract "My Text?" and not "My Text? " (with the trailing space). If that becomes important then you'll need to use something like this:

=MID(A1,FIND(CHAR(1),SUBSTITUTE(A1,"/",CHAR(1),3))+1,FIND(CHAR(1),SUBSTITUTE(A1,"/",CHAR(1),4))-FIND(CHAR(1),SUBSTITUTE(A1,"/",CHAR(1),3))-1)

That formula finds the exact locations of the "/" characters in the string and extracts the text between them. Change the highlighted 3 and 4 to change which occurrences of "/" it looks for. For example, changing the 4 to 5 will return "My Text? /vwx".

Regards,

WBD

 

[Snakehips]

Hi Tony,

Could you explain this I've looked at the individual formulas but could do with a break down so I can understand this and be able to use it if I need to look at any other positions.

Pipped to it by WBD.

Always best to use big number of substitute spaces but if you try a small number example then you can see it if you use F9 to evaluate each element.

Excel Workbook
	A	B
2	/abc/def/XXX/1234
3	abc def XXX 1234
4	XXX
5	XXX
Sheet13

Glad

 

[seekerfaith]

Thank you to both for the explanation, the visual aid was very useful for me to understand.

I've noticed that the data still needs some further progressing, I need to check the result if it contains "QAS " OR "QAS" with a string i.e. "/ABC/ABC/QASUNTITLED/123" or "/ABC/ABC/QAS UNTITLED/123" if so then apply the SUBSTITUTE above minus the first 3/4 characters. Any Ideas?

 

[Snakehips]

Excel Workbook
	A	B
1	/ABC/ABC/QAS UNTITLED/123	UNTITLED
Sheet13

 

[Peter_SSs]

A few comments

Always best to use big number of substitute spaces ..

I'd add "within reason". It does take Excel longer to evaluate REPT(" ",999) than to evaluate, say, REPT(" ",100).
So if you have a lot of these formulas, you can impair the sheet's calculation performance if you choose an unnecessarily large number. To balance that, you do need to choose one that is big enough to do the job.
If the data is all similar to the sample in post #1, then a much smaller 'REPT' number (say 50) should be sufficient (with appropriate adjustments to the other two numbers in the formula of course).

There is small downside to the formula in that from "/abc/cde/My Text? /vwx/1234" it will extract "My Text?" and not "My Text? " (with the trailing space). If that becomes important then you'll need to use something like this:

=MID(A1,FIND(CHAR(1),SUBSTITUTE(A1,"/",CHAR(1),3))+1,FIND(CHAR(1),SUBSTITUTE(A1,"/",CHAR(1),4))-FIND(CHAR(1),SUBSTITUTE(A1,"/",CHAR(1),3))-1)

Assuming that we are still just trying to extract the third term in the string, and preserve any leading/trailing spaces, then a shorter way would be as follows, where "|" is a character chosen that will not appear in your actual text. This has the added advantage of not returning an error if pointed at an empty cell or a cell that does not contain at least 4 "/" characters.

=SUBSTITUTE(TRIM(MID(SUBSTITUTE(SUBSTITUTE(A1," ","|"),"/",REPT(" ",50)),150,50)),"|"," ")

I've noticed that the data still needs some further progressing, I need to check the result if it contains "QAS " OR "QAS" with a string i.e. "/ABC/ABC/QASUNTITLED/123" or "/ABC/ABC/QAS UNTITLED/123" if so then apply the SUBSTITUTE above minus the first 3/4 characters. Any Ideas?

If I have understood your requirement, then I think this simpler formula does what you want.

=TRIM(SUBSTITUTE(MID(SUBSTITUTE(A1,"/",REPT(" ",100)),300,100),"QAS",""))

 

[Snakehips]

A few comments

I'd add "within reason"........


If I have understood your requirement, then I think this simpler formula does what you want.

=TRIM(SUBSTITUTE(MID(SUBSTITUTE(A1,"/",REPT(" ",100)),300,100),"QAS",""))

Peter,
REPT comment appreciated and understood. I should perhaps have said 'sufficient' spaces. I have been caught out in the past where OP's actual text requires far more space substitution than their posted examples might suggest so have developed the habit of erring on the high side.

I had initially read previous post as wishing to extract the third substring only if it contains QAS but now see that it is wanting 'further processing' in order to clean up of original result if it contains QAS.
On that basis Peter's formula is the one to go with.

 

[Peter_SSs]

I have been caught out in the past where OP's actual text requires far more space substitution than their posted examples might suggest so have developed the habit of erring on the high side.

Tony, Fair enough & understood. I just didn't want the OP to maybe go away and use 10000 when 100 might do.