Extracting information from Text Strings in Excel

Extracting information from Text Strings in Excel

Didn't find your answer?

I have two possible types of text string in Excel, either DA3-AJ33S-5P1 or DA3-AJ150S-5P1.

I want a formula that extracts the middle part of the text string i.e. the "AJ33S" or the "AJ150S" - the problem i am having is that the two text strings can be different lengths.

Any help would be greatly appreciated.

Replies (7)

Please login or register to join the discussion.

avatar
By Richard Willis
25th Jun 2012 10:36

Jason

Try

=MID(B3,5,LEN(B3)-8) 

Where B3 contains the string

Thanks (0)
By George Attazder
25th Jun 2012 10:55

Richard's not wrong (completely)...

... but his response does assume that the thing that changes the length of the string occurs after the part being looked for. I assume though what is constant is the "-" separators.

In which case you'd do better to break it down a bit.

Assuming your text string is in A1, I'd put =FIND("-",A1)+1 in B1, =FIND("-",A1,B1) in C1, and =MID(A1,B1,C1-B1) in D1. D1 will then show you the section you're looking for between the dashes.

You can put this into a single formula, but it would be quite cumbersome and look horrible, which means others won't then understand it.

Thanks (0)
avatar
By Richard Willis
25th Jun 2012 11:05

Eh?!

My assumption is that the prefix (DA3-) and the suffix (-5P1) are of constant length (4 each, therefore 8).  Not sure what you mean George!

As long as my assumption is correct, which from the OP it should be, my solution should work.

SO =Mid(source,start position, No of characters) is Mid(source,5(being 'DA3-' plus1),No of characters (= len(source)-8 as above))

This will work for any length of string as long as my assumption about the prefix and suffix is correct.

Thanks (0)
avatar
By jasongibson
25th Jun 2012 11:04

Sucess

Richard - your formula seems to have worked, it is picking out the desired text from the text string - thanks for your help.

Thanks (0)
avatar
By Richard Willis
25th Jun 2012 11:06

Jason

No problem; glad to help.

Thanks (0)
By George Attazder
25th Jun 2012 11:08

Apologies

You're correct Richard. I'd assumed that when the OP said that the strings could be different lengths, it was any of the three elements could vary in length.

If your assumptions are correct, I agree that it's a simpler solution.

Thanks (0)
avatar
By cparker87
25th Jun 2012 12:26

.

Just use Data>"Text to Columns" with "-" as the as the delimited seperating character.

Thanks (0)