Formula Help to Extract Partial Data from a String

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.

--
Steph

--
Steph

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

On Mar 10, 11:46*pm, Steph wrote:
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.

--
Steph

--
Steph

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)


--
Biff
Microsoft Excel MVP


"Steph" wrote in message
...
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.

--
Steph

--
Steph

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

"Steph" wrote in message
...
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.

--
Steph

--
Steph

On Tue, 10 Mar 2009 16:46:11 -0700, Steph
wrote:

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.

--
Steph

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