Formula Help to Extract Partial Data from a String

[Steph]

I have data that looks like this:

S30 1M D C7D
S30 1M D
5W I 4
2W I X AH1

I need a formula that will extract the third group of characters from each
record. The records can be any length and the groups are seperated by
spaces. For example,

From "S30 1M D C7D" I need to extract "D".
From "S30 1M D" I need to extract "D".
From "5W I 4" I need to extract "4".
From "2W I X AH1" I need to extract "X".

Thanks for any help that can be provided.

 

[Pete_UK]

If you highlight the data and click on Data | Text-to-columns, then
choose delimited with space as the delimiter, then the characters you
require will be in the third column.

Hope this helps.

Pete

 

[T. Valko]

Assuming there will *always* be at least 3 groups of characters.

Broken into chunks so line wrap doesn't take out the needed space
characters.

=LEFT(MID(A2,FIND(" ",A2,FIND
(" ",A2)+1)+1,255),FIND
(" ",MID(A2&" ",FIND(" ",A2,FIND
(" ",A2)+1)+1,255))-1)

 

[Ashish Mathur]

Hi,

This is an alternative solution.

=MID(E17,SEARCH(" ",E17,SEARCH(" ",E17,1)+1)+1,IF(ISERROR(SEARCH("
",E17,SEARCH(" ",E17,SEARCH(" ",E17,1)+1)+1)-SEARCH(" ",E17,SEARCH("
",E17,1)+1)),50,SEARCH(" ",E17,SEARCH(" ",E17,SEARCH("
",E17,1)+1)+1)-SEARCH(" ",E17,SEARCH(" ",E17,1)+1)))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Ron Rosenfeld]

I have data that looks like this:

S30 1M D C7D
S30 1M D
5W I 4
2W I X AH1

I need a formula that will extract the third group of characters from each
record. The records can be any length and the groups are seperated by
spaces. For example,

From "S30 1M D C7D" I need to extract "D".
From "S30 1M D" I need to extract "D".
From "5W I 4" I need to extract "4".
From "2W I X AH1" I need to extract "X".

Thanks for any help that can be provided.

One other method. You could download and install Longre's free morefunc.xll
add-in (Google for a download site), and then use this Regular Expression
formula:

=REGEX.MID(A1,"\w+",3)

--ron